question about join on multiple column

[liuxia0510]

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.

code:

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

[mkracker]

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