question about join on multiple column

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: 4
Joined: Sun May 31, 2015 7:13 pm

question about join on multiple column

Post by liuxia0510 » Tue Jun 16, 2015 9:05 pm

I have a set of specific patent, for example, appln_id= 16429394, I expect to set up another set of samples, which have the same filing year and ipc 4-digit classification with this patent. The code works well at my local computer, but can't work on patstat online database. Is there anything I can do? many thanks.


select* from tls201_appln t4 join tls209_appln_ipc t5 on t4.appln_id=t5.appln_id
join (select appln_filing_year, ipc_subclass_symbol from tls201_appln t1
join tls209_appln_ipc t2 on t1.appln_id=t2.appln_id where t1.appln_id=16429394) t3
on t4.appln_filing_year=t3.appln_filing_year and t5.ipc_subclass_symbol=t3.ipc_subclass_symbol

Posts: 114
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: question about join on multiple column

Post by mkracker » Wed Jun 17, 2015 12:19 pm

Hi liuxia0510,

Your query is correct and should run on PATSTAT Online. We will analyse and correct this issue.

As a work around, I reformulated your query. IMO it has a simpler structure now, returns the same result and runs on PATSTAT Raw Data as well as on PATSTAT Online. I also added the DISTINCT clause to avoid duplicates in the result.

Code: Select all

-- retrieve all applications and their IPCs which have the same filing year and one of the IPCs of appln_ID 16429394
-- tables a1 and i1 are used for output
-- tables a2 and i2 are used for restricting the output

-- returns 30 479 rows on 2015 Spring Edition
select distinct a1.*, i1.*
from tls201_appln a1
join tls209_appln_ipc i1 on a1.appln_id=i1.appln_id
join tls201_appln a2 on year(a1.appln_filing_date) = year(a2.appln_filing_date)
join tls209_appln_ipc i2 on a2.appln_id=i2.appln_id
						and left(i1.ipc_class_symbol, 4) = left(i2.ipc_class_symbol, 4)
where a2.appln_id=16429394
Best regards,
Martin Kracker / EPO

Post Reply