I am currently working on my MSc thesis and i am trying to interrogate the EP Register from PATSTAT Online to retrieve a list of patent applications' register number and the inventor's and applicant's country, but i receive the following error:
"[SELECT - 0 row(s), 0 secs] [Error Code: 8156, SQL State: S0001] The column 'id' was specified multiple times for 'applicant'."
I don't know what i am doing wrong.. Can someone help me please? Thank you.
Here it is the SQL query that i run:
Code: Select all
SELECT distinct appln_auth, appln_nr, appln_filing_date
, inventor.country Inventor_country, applicant.country Applicant_country
FROM reg101_appln join reg110_title on reg101_appln.id = reg110_title.id
join (SELECT distinct reg110_title.id, reg107_parties.id, reg107_parties.country
FROM reg107_parties join reg110_title
on reg107_parties.bulletin_year = reg110_title.bulletin_year and reg107_parties.bulletin_nr = reg110_title.bulletin_nr and reg107_parties.type like 'A' ) applicant
on reg101_appln.id = applicant.id
join (SELECT distinct reg110_title.id, reg107_parties.id, reg107_parties.country
FROM reg107_parties join reg110_title
on reg107_parties.bulletin_year = reg110_title.bulletin_year and reg107_parties.bulletin_nr = reg110_title.bulletin_nr and reg107_parties.type like 'I' ) inventor
on reg101_appln.id = inventor.id
where reg101_appln.appln_filing_date >= '2014/01/01' and reg101_appln.appln_filing_date <= '2020/04/30' and inventor.country like 'RO' and applicant.country like 'RO'