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,
Sandra
I tried it with several approaches, e.g.:
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'