SET group_concat_max_len=18446744073709551615;
/* EXPLAIN */
SELECT
GROUP_CONCAT(DISTINCT(ms.synonyms) SEPARATOR ';') AS compound_names,
cs.accession AS target_uniprot,
md.chembl_id AS compound_chembl,
td.tax_id AS tax_id,
tt.target_type AS target_type ,
GROUP_CONCAT(DISTINCT(dm.action_type) SEPARATOR ';') AS action_type,
CAST(cp.alogp AS CHAR) AS alogp,
CAST(cp.acd_logp AS CHAR) AS acd_logp,
GROUP_CONCAT(DISTINCT(ac.pchembl_value) SEPARATOR ';') AS pchembl,
md.max_phase AS max_phase
FROM component_sequences AS cs
INNER JOIN target_components AS tc ON tc.component_id = cs.component_id
INNER JOIN target_dictionary AS td ON tc.tid = td.tid
INNER JOIN target_type AS tt ON td.target_type = tt.target_type
INNER JOIN assays AS ay ON td.tid = ay.tid
INNER JOIN activities AS ac ON ay.assay_id = ac.assay_id
INNER JOIN molecule_dictionary AS md ON ac.molregno = md.molregno
LEFT JOIN molecule_synonyms AS ms ON md.molregno = ms.molregno
LEFT JOIN drug_mechanism AS dm
ON (dm.molregno = md.molregno AND dm.tid = td.tid)
/* various properties of the compounds */
LEFT JOIN compound_properties AS cp
ON md.molregno = cp.molregno
WHERE
tt.parent_type = "PROTEIN" AND
ac.potential_duplicate != 1 AND
ac.pchembl_value IS NOT NULL AND
(
data_validity_comment IS NULL OR
data_validity_comment = 'manually validated'
) AND
ay.assay_type IN ("B") AND
ay.relationship_type IN ("D","H") AND
cs.accession IN ("P31749","P31751","Q9Y243","O14578","Q09013","Q5VT25","Q9Y5S2","Q6DT37","Q13464","O75116","P25098","P35626","P32298","P34947","P43250","Q15835","Q8WTQ7","Q9Y2H9","O60307","Q6P0Q8","Q96GX5","O95835","Q15208","Q9Y2H1","Q9NRM7","O15530","P17612","P22694","P22612","P51817","O43930","P17252","P05771","P05129","Q05655","Q04759","Q02156","P24723","P41743","Q05513","Q13976","Q13237","Q16512","Q16513","Q6P5Z2","O75676","O75582","P23443","Q9UBS0","Q15418","Q15349","P51812","Q9UK32","Q96S38","Q9Y6S9","O00141","Q9HBY8","Q96BR1","Q9NY57","Q8WU08","Q86UX6","Q8NI60","Q96D53","Q86TW2","Q3MIX3","Q7Z695","Q96L96","Q96QP1","Q86TB3","Q96QT4","Q9BX84","O00418","P11274","Q58F21","P25440","Q15059","O60885","Q14296","P49842","Q9UJY1","O14874","Q15118","Q15119","Q15120","Q16654","Q13315","Q13535","P78527","P42345","Q96Q15","Q9Y4A5","Q9BRS2","Q9BVS4","O14730","P21675","Q8IZX4","O15164","Q13263","Q9UPN9","Q16566","Q14012","Q8IU85","Q96NX5","Q6P2M8","Q9UQM7","Q13554","Q13557","Q13555","Q13131","P54646","Q8IWQ3","Q8TDC3","O14757","P57058","Q15831","Q7KZI7","Q9P0L2","P27448","Q96L34","Q14680","O60285","Q9H093","Q96RG2","Q9H0K1","Q9Y2K2","P57059","Q9NRH2","Q9BYT3","Q8NCB2","O14936","P53355","O43293","Q9UIK4","O94768","Q9UEE5","O15075","Q9C098","Q8N568","Q16644","Q8IW41","P49137","Q9HBH9","Q9BUB5","Q15746","Q8WZ42","Q9H1R3","Q32MK0","Q16816","P15735","P11309","Q9P1W9","Q86V86","Q15139","O94806","Q9BZL6","P11801","Q96QS6","O96017","Q8N2I9","Q96RU8","Q92519","Q96RU7","Q5VST9","O75962","O60229","Q96PF2","Q96PN8","Q9BXA7","Q9BXA6","Q6SA08","P48729","Q8N752","P48730","P49674","P78368","Q9Y6M4","Q9HCP0","Q5TCY1","Q6IQ55","Q99986","Q86Y07","Q8IV63","Q8IZL9","P06493","P24941","Q00526","Q15131","P11802","Q00534","Q00535","P50613","P49336","Q9BWU1","P50750","Q14004","Q9NYV4","Q00536","Q00537","Q07002","O94921","Q96Q40","Q9UQ88","O76039","Q00532","Q92772","Q8IVW4","Q5MAI5","P68400","P19784","P49759","P49760","P49761","Q9HAZ1","Q13627","Q9Y463","O43781","Q92630","Q9NR20","Q9H422","Q9H2X6","Q8NE63","Q86Z02","Q13523","P49840","P49841","P28482","P27361","P31152","Q16659","Q13164","Q8TD08","P45983","P45984","P53779","Q9UBE8","Q16539","Q15759","O15264","P53778","P20794","Q15109","Q9UQ07","Q9UPZ9","Q96SB4","P78362","Q9UPE1","O14965","Q9UQB9","Q96GD4","O43683","O60566","Q96S44","Q96RR4","Q8N5S9","O00311","Q8TF76","O15111","O14920","Q14164","Q9UHD2","O75460","Q76MJ5","Q8TAS1","P00540","Q2M2I8","Q9NSY1","O14976","O75716","Q96PY6","P51956","Q6P3R8","Q6ZWH5","Q8NG66","P51955","P51957","Q9HC98","Q8TDX7","Q8TD19","Q86SG6","Q52WX2","Q9BXM7","Q9H792","Q8TDR2","Q8N165","Q8IWB6","Q9UHY1","Q9NSY0","Q05823","Q9BXU1","Q9P2K8","Q9BQI3","Q9NZJ5","P19525","P53350","Q9H4B4","Q9NYY3","O00444","Q6P3W7","Q8IZE3","Q96KG9","Q8NE28","Q6XUX3","Q7Z7A4","Q9UKI8","Q86UE8","Q96KB5","P33981","Q9NRP7","O75385","Q8IYT8","Q6PHR2","Q96C45","Q99570","P30291","Q99640","Q9H4A3","Q96J92","Q9BYP7","Q9Y3S1","P25092","P51841","Q02846","P16066","P20594","Q99683","O95382","Q6ZN16","Q13233","Q56UN5","Q99759","Q9Y2U5","Q9Y6R4","O95747","Q9UEW8","Q12851","Q8IVH8","Q9Y4K4","Q92918","O95819","Q9UKE5","Q7Z2Y5","Q8N4C8","Q13188","Q13043","Q8NEV4","Q8WXR4","Q13153","Q13177","O75914","O96013","Q9NQU5","Q9P286","O94804","Q9H2G2","Q9C0K7","Q7RTN6","Q9UL54","Q9H2K8","Q7L7X3","Q9Y6E0","O00506","Q02750","P36507","P46734","P52564","P45985","Q13163","O14733","P41279","Q99558","P00519","P42684","Q07912","Q13470","Q9UM73","P29376","P30530","Q06418","Q12866","Q13308","P41240","P42679","Q08345","Q16832","P00533","P04626","P21860","Q15303","P29317","P21709","P29320","P54764","P54756","Q15375","P29322","P54762","P29323","P54753","P54760","O15197","Q5JZY3","Q9UF33","Q14289","Q05397","P16591","P07332","P11362","P22607","P21802","P22455","P08069","P06213","P14616","P23458","O60674","P52333","P29597","Q6ZMQ8","Q8IWU2","Q96Q04","P08581","Q04912","O15146","P07333","P36888","P10721","P16234","P09619","P07949","Q01973","Q01974","P34925","P08922","P42685","P09769","P06241","P12931","P07947","P51451","P08631","P06239","P07948","Q13882","Q9H3Y6","P43405","P43403","P51813","Q06187","Q08881","P42680","P42681","Q02763","P35590","Q6J9G0","P04629","Q16620","Q16288","P17948","P35916","P35968","P51617","O43187","Q9Y616","Q9NWZ3","P53667","P53671","Q15569","Q96S53","Q38SD2","Q5S007","Q59H18","Q13418","Q12852","O43283","P80192","Q02779","Q16584","Q5TCX8","O43318","Q9NYL2","Q8IVT5","Q6VAB6","P10398","P15056","P04049","Q13546","O43353","Q9Y572","P57078","Q8NFD2","Q04771","P36896","P37023","P36894","O00238","P36897","Q8NER5","P27037","Q13705","Q16671","Q13873","P37173","Q8NB16")
GROUP BY cs.accession,md.chembl_id
ORDER BY NULL;
