SQLIte: Table joins

-- Extract all data from index table and rows matching specified fields from joined tables
SELECT * FROM dss_tox 
    JOIN pubchem_cids ON pubchem_cids.smiles = dss_tox.smiles
;

-- Extract data from specified fields across multiple tables

SELECT dss_tox.cas_number, pubchem_chemidplus.chemidplus_id, pubchem_cids.pubchem_cid, dss_tox.substance_name, dss_tox.smiles 
    FROM dss_tox 
    LEFT JOIN pubchem_cids ON pubchem_cids.smiles = dss_tox.smiles 
    LEFT JOIN pubchem_chemidplus ON pubchem_chemidplus.pubchem_cid = pubchem_cids.pubchem_cid
;

"The royal we…."