-- This script uses the table tls206x_person table generated by the script 
-- provided by GianLuca Tarasconi, see http://rawpatentdata.blogspot.com 
-- for more info

USE patstatApr2013;

-- File 3 This gives all patents with CPC_CLASS_SYMBOL
SELECT t1.appln_id, CONCAT(t1.appln_auth, t1.appln_nr), t1.appln_filing_date FROM tls201_appln AS t1
INNER JOIN tls224_appln_cpc AS t2
ON t1.appln_id = t2.appln_id
AND cpc_class_symbol LIKE "%Y02E%10/541%"
INTO outfile 'applnyear.csv';

-- File 1  with inventors incuded in tls206x_person
-- this script assumes an extra column with full country name associated with ctry_code
SELECT distinct t1.appln_id, CONCAT(t1.appln_auth, t1.appln_nr), city, ctry_code, country_name, invt_seq_nr
FROM 
(tls201_appln AS t1 INNER JOIN tls224_appln_cpc AS t3 
ON t3.cpc_class_symbol LIKE "%Y02E%10/541%" 
AND t1.appln_id = t3.appln_id )
LEFT JOIN (tls207_pers_appln AS t4 INNER JOIN tls206x_person AS t2
ON t4.person_id = t2.person_id AND applt_seq_nr = 0 )
ON t1.appln_id = t4.appln_id
ORDER BY t1.appln_id, invt_seq_nr
INTO OUTFILE 'address.csv';



-- File 2 
SELECT t1.appln_id, t1.appln_auth, t1.appln_nr, t2.ipc_class_symbol 
FROM ( tls201_appln AS t1
INNER JOIN tls224_appln_cpc AS t3 on t1.appln_id = t3.appln_id
AND t3.cpc_class_symbol LIKE "%Y02E%10/541%") 
LEFT JOIN tls209_appln_ipc AS t2
ON t1.appln_id = t2.appln_id
ORDER BY t1.appln_id ASC
INTO OUTFILE 'applnipc.csv';