Query for my bachelor thesis

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Posts: 1
Joined: Sun Mar 16, 2014 2:25 am

Query for my bachelor thesis

Post by skar » Sun Mar 16, 2014 7:34 pm

Dear all,

I have been trying all weekend to get the query work to answer the question below:

Are the Chinese industrial textile machines based on technology from the well established Western producers?

I thought I could translate it into the following question for PATSTAT:
What countries and companies are the Chinese patents for D01H, D01G und B65H citing from?

Later, I would like to download and analyze them in Excel Power Pivot.
Can someone help here?

Many thanks in advance and best regards,


I tried it with several approaches, e.g.: :roll:

SELECT tls209_appln_ipc.ipc_subclass_symbol, tls209_appln_ipc.appln_id, tls209_appln_ipc.ipc_gener_auth, tls211_pat_publn.pat_publn_id, tls211_pat_publn.appln_id, tls212_citation.pat_publn_id, tls212_citation.cited_appln_id, tls212_citation.citn_gener_auth, tls212_citation.cited_pat_publn_id
FROM (tls209_appln_ipc INNER JOIN tls212_citation ON tls209_appln_ipc.appln_id = tls212_citation.cited_appln_id) INNER JOIN tls211_pat_publn ON (tls212_citation.pat_publn_id = tls211_pat_publn.pat_publn_id) AND (tls209_appln_ipc.appln_id = tls211_pat_publn.appln_id)
WHERE (((tls209_appln_ipc.ipc_subclass_symbol)="D01H")) OR (((tls209_appln_ipc.ipc_subclass_symbol)="D01G")) OR (((tls209_appln_ipc.ipc_subclass_symbol)="B65H"));

or before that with:

select distinct tls209_appln_ipc.appln_id, tls209_appln_ipc.ipc_class_symbol, tls212_citation.citn_id, tls212_citation.cited_appln_id, tls207_pers_appln.person_id, tls207_pers_appln.appln_id, tls207_pers_appln.applt_seq_nr, tls206_person.person_id, tls206_person.person_ctry_code, tls206_person.person_name , tls206_person.person_address
from tls209_appln_ipc
where tls209_appln_ipc.ipc_class_symbol = 'D01H' or tls209_appln_ipc.ipc_class_symbol = 'D01G' or tls209_appln_ipc.ipc_class_symbol = 'B65H'
inner join tls212_citation on tls209_appln_ipc.appln_id = tls212_citation.citn_id
inner join tls207_pers_appln on tls209_appln_ipc.appln_id = tls207_pers_appln.appln_id
inner join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id

and also:

Select *

from tls209_appln_ipc

left outer join tls212_citation on tls209_appln_ipc.appln_id = tls212_citation.citn_id,
left outer join tls207_pers_appln on tls209_appln_ipc.appln_id = tls207_pers_appln.appln_id,
left outer join tls206_person on tls209_appln_ipc.appln_id = tls206_person.person.id

where tls209_appln_ipc.ipc_class_symbol = 'D01H' or tls209_appln_ipc.ipc_class_symbol = 'D01G' or tls209_appln_ipc.ipc_class_symbol = 'B65H'

Post Reply