Hello Xiaohan,
The reason why you don't find any result is because of the way your query is build using t2.ipc_class_symbol LIKE 'C10L 3/00'
AND 'F02C 1/05'. This is correct SQL syntax, but it does not work for what you intend to do.
SQL and relational data bases have what we call normalised data, so each attribute (mostly) contains only 1 single value. With other words ipc_class_symbol can not contain multiple values in the same "field".
Example: If you run the query below you will find 1 patent having 4 IPC codes.
Code: Select all
Select appln_auth, appln_nr, appln_kind, ipc_class_symbol from
tls201_appln
join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id
where
tls201_appln.appln_auth = 'EP' and tls201_appln.appln_nr= '07808942'
If you would change this now to :
Code: Select all
Select appln_auth, appln_nr, appln_kind, ipc_class_symbol from
tls201_appln
join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id
where
tls201_appln.appln_auth = 'EP' and tls201_appln.appln_nr= '07808942'
and ipc_class_symbol = 'C10L 3/00'
and ipc_class_symbol = 'F02C 1/06'
You will get 0 results, even though we know that both codes are correct IPC codes for that patent. This is because the data base looks for both values in the same record.
A correct query that does what you need is:
Code: Select all
Select appln_id,appln_auth, appln_nr, appln_kind
FROM tls201_appln
WHERE
tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol = 'C10L 3/00')
and
tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol = 'F02C 1/06')
2 "small" observations:
a) using LIKE with % wildcard is in most cases sufficient to also cover the lower groups of the classification, but not always. To be 100% sure, double check in ESPACENET.
See:
https://worldwide.espacenet.com/patent/ ... C=F02C1/05, and you will observe that "LIKE 'F02C 1/05%' would not cover 'F02C 1/06', which I assume you want to also include to obtain all the patents in that technical field. Here you can use the IN clause which is the similar to using OR.
b) for most data extractions, you would want to cover as well IPC as CPC to retrieve all relevant patents.
So here is a solid query that gives you all patents that have both the IPC codes or both the CPC codes. (The classfication codes are in an AND relation, the IPC and CPC conditions in an OR !)
Code: Select all
Select appln_id,appln_auth, appln_nr, appln_kind
FROM tls201_appln
WHERE
(
tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol like 'C10L 3%')
and
tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol in ('F02C 1/05','F02C 1/06' ) )
)
or
(
tls201_appln.appln_id in (select appln_id from tls224_appln_cpc where cpc_class_symbol like 'C10L 3%')
and
tls201_appln.appln_id in (select appln_id from tls224_appln_cpc where cpc_class_symbol in ('F02C 1/05','F02C 1/06' ) )
);
If you want to extend this to even more IPC or CPC codes, then you will need to extend the WHERE close with more conditions via ORs.
This query only retrieves 11 patents, so not much counting can be done over applicants and years. So I am doubtful if your combination of 'C10L 3%' and ('F02C 1/05','F02C 1/06' ) is really what you want.
To download a data set, you just have to be sure that the SELECT clause contains the appln_id. So not your query with the CASE and COUNTS.