PubChem Substance select

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. 

From the examples in the previous section, it is clear how the substance id relates pubchem.substance to biological assay data and how substance data can be selected using the substance id. How can the compound table be used to select compound data for substances appearing in one of the biological assay data tables In other words, how is the... 

The column pubchem.substance.cid associations is taken directly from the sdf files supplied by PubChem. It has all the necessary information, but it is not in a proper form for a relation between pubchem.substance and pubchem.compounds. This is because too much information has been crammed into this column. For example, the cid associations for substance id 22 contains the data "449653 1449655 2 6540406 2". This means that there are three compound ids associated with this substance id. In other words, there is a many-to-many relationship between compounds and substances. While it would be possible to parse the cid associations column when the compound id is needed, it is better to have a clear relationship between substance ids and compounds ids. It is better because it enforces and preserves the relational integrity (or referential integrity) between these data. It also makes selecting data from all three data sources quicker and easier. 

After working with PubChem to achieve a particular subset for a query of inferesf, it is often important for a user to export resulting substance or compound records from PubChem for further local analysis. The structure download tool prepares PubChem Substance or PubChem Compound records as an export from Enfrez in a number of formats. While all PubChem data is available on the PubChem FTP site (via the URL ftp //ftp.ncbi.nlm.nih.gov/pubchem/), being able to interact with a user-selected subset is substantially more convenient. The structure download tool may be directly accessed using the URL ... 

When the desired data is in two different tables, a join is required. It is often helpful to begin to develop complex SQL statements by considering one table at a time. For example, data from the nci h23 table of pubchem schema was considered earlier in this chapter. The experimental data to be selected from that table was the substance id (called sid in the table), activity outcome, log gi50 M and n.log gi50 ugml. This is accomplished by the simple SQL... 

BioActivity summary provides a set of functions that allows one to revise the substance/compound and assay sets. For example, one may focus only on a subset of compounds that are active in one or more of the selected assays using the Compound I Select Active link, or explore additional screen sets where the given compounds were considered active using the Assay I Add Active link. PubChem provides multiple access points for this service. For compounds or substances tested found in Entrez, one can launch this service for each individual record using the direct "BioActivity Analysis" link, or, for all of the records from an Entrez search, through the launching point at the "Tool" area. 

---