Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 57

Title,
BONDANNOTATIONS,
CID_ASSOCIATIONS,
COMPOUND_ID_TYPE,
EXT_DATASOURCE_NAME,
EXT_DATASOURCE_REGID,
EXT_DATASOURCE_URL,
EXT_SUBSTANCE_URL,
GENBANK_NUCLEOTIDE_ID,
GENBANK_PROTEIN_ID,
GENERIC_REGISTRY_NAME,
PUBMED_ID,
SUBSTANCE_COMMENT,
SUBSTANCE_ID,
SUBSTANCE_SYNONYM,
SUBSTANCE_VERSION,
TOTAL_CHARGE,
XREF_EXT_ID)
From stdin delimiter ',';
1,\N,449635 1,0,MOLI,MOLI000002,\N,\N,\N,\N,\N,\N, MOLI - NCI
Molecular Imaging Agents\nFGCV,1,MOLI000002,1,0,MOLI000002
2,2 11 5\n20 34 5\n25 31 6\n28 55 5\n5 13 6\n58 59 6\n8 33 5,\N,0,
MOLI,MOLI000003,\N,\N,\N,\N,\N,\N,MOLI - NCI Molecular Imaging
Agents\n[99mTc]-P2S2-BBN(7-14),2,MOLI000003,1,0,MOLI000003
The column names in the Create statement are taken directly from the
data tags in the input sdf file. The data types are guessed after analysis of
data in the file. This sample includes only two lines of actual data from the
13,036 entries in the file.
Once this substance table is in place, it is now possible to select in a
single SQL statement any substance data along with nci _ h23 data. The
previous statement can be modified as follows:
Select
sid, ext_datasource_name, substance.ext_datasource_regid ,
activity_outcome, "log_gi50_M", log_gi50_ugml
From
pubchem .nci_h23
Join pubchem.substance On substance.substance_id = nci_h23.sid
Where activity_outcome = 2;
Notice the use of the Join keyword and the additional table name
pubchem.substance in the From clause. This is necessary because
data from this table is being selected. The additional columns selected
are ext _ datasource _ name and substance.ext _ datasource _
regid in the Select clause. Any columns of interest in the substance
table could be selected. Note that since there is a column named ext _
datasource _ id in both tables, it is necessary to specify that the column
substance.ext_datasource_regid is desired . Finally, the clause On nci _
h23.sid = substance.substance _ id indicates that these columns are
related to each other and must be used in the Join .