Anyway, I am trying to retrieve patents with at least one inventor (or applicant) located in Norway. I get back the results I want, but if the patent has inventors that are not located in Norway (co-inventors) then those inventors do not appear in the list. So I suppose I have somewhere a mistake (or a misunderstanding) at the syntax of my query. The code is the following:
Code: Select all
SELECT DISTINCT t1.appln_id AS ApplicationID1, t1.appln_auth AS ApplicationAuthority, t1.appln_nr AS ApplicationNumber, t1.nb_applicants AS NumberOfApplicants, t1.nb_inventors AS NumberOfInventors, t1.earliest_publn_year AS EarliestPublicationYear, t1.granted AS IsItGranted,
t2.cpc_class_symbol AS CPCClassSymbol,
t3.appln_title AS ApplicationTitle,
t4.pat_publn_id AS PublicationID, t4.publn_auth AS PublicationAuthority, t4.publn_nr AS PublicationNumber, t4.publn_kind AS PublicationKind, t4.publn_date AS PublicationDate,
t5.person_id AS PersonID, t5.applt_seq_nr AS PlaceListApplicants, t5.invt_seq_nr AS PlaceListInventors,
t6.person_name AS PersonName, t6.person_address AS PersonAddress, t6.person_ctry_code AS PersonCountryCode, t6.nuts AS NUTSRegion, t6.nuts_level AS NUTSLevel, t6.psn_name AS PersonPATSTATName, t6.psn_id AS PersonPATSTATID, t6.psn_sector AS PersonSector,
t9.last_name AS PersonLastName, t9.first_name AS PersonFirstName, t9.address_freeform AS AddressFreeform, t9.city AS PersonCity, t9.zip_code AS PersonZIPCode, t9.state AS PersonState, t9.person_ctry_code AS PersonCountryCode
FROM tls201_appln t1
FULL JOIN tls224_appln_cpc t2 ON t1.appln_id = t2.appln_id
FULL JOIN tls202_appln_title t3 ON t1.appln_id=t3.appln_id
FULL JOIN tls211_pat_publn t4 ON t1.appln_id = t4.appln_id
FULL JOIN tls207_pers_appln t5 ON t1.appln_id = t5.appln_id
FULL JOIN tls206_person t6 ON t5.person_id = t6.person_id
FULL JOIN tls226_person_orig t9 ON t5.person_id = t9.person_id
WHERE t6.person_ctry_code = 'ΝΟ'
AND
t4.publn_date BETWEEN '2008-01-01' AND '2008-12-31'
AND t1.granted = 'Y'
ORDER BY t1.appln_id;
Thanks in advance!
Best,
Maria