NAV

SQL Examples

Basic Queries

Show the first 10 items in a table (e.g. drugs):
SELECT * FROM drugs LIMIT 10;
Get the total number of items in a table (e.g. drugs):
SELECT COUNT(*) FROM drugs;
Get the number of each record type (Drug, Category, Metabolite, etc.) in the database:
SELECT record_type, COUNT(*) FROM accession_numbers GROUP BY record_type;

Drug Queries

Drugs are linked to categories in a many-to-many relationship via the drug_categorizations table. To get the categories for each drug:
SELECT d.name, d.drugbank_id, c.title AS category FROM drugs d
  JOIN drug_categorizations dc ON dc.drug_id = d.id
  JOIN categories c            ON dc.category_id = c.id
  ORDER BY d.drugbank_id;
Get drugs and their pharmacology information:
SELECT d.drugbank_id, d.name, p.* FROM drugs d
  JOIN pharmacologies p ON d.id = p.drug_id
  ORDER BY d.drugbank_id;
Get drugs and their reactions:
SELECT d.drugbank_id, d.name, r.* FROM drugs d
  JOIN reactions r ON r.drug_id = d.id
  ORDER BY d.drugbank_id;
Drug interactions are found in the structured_drug_interactions table. This is linked to the drugs table via the subject_drug_id and affected_drug_id columns. To get drugs and their interactions with other drugs:
SELECT d.drugbank_id, d.name, sdi.* FROM drugs d
  JOIN structured_drug_interactions sdi ON sdi.subject_drug_id = d.id OR sdi.affected_drug_id = d.id
  ORDER BY d.drugbank_id;
Drug categories are linked to different vocabularies in the category_mappings table. To get drugs with their categories and category ATC codes:
SELECT DISTINCT drg.name, cat.title AS category_title, map.code, map.vocabulary_level FROM drugs drg
  JOIN drug_categorizations dcs ON drg.id = dcs.drug_id
  JOIN categories cat           ON cat.id = dcs.category_id
  JOIN category_mappings map    ON map.category_id = cat.id and map.vocabulary = 'ATC'
  ORDER BY drg.name;
Drugs are linked to different vocabularies in the drug_mappings table. To get drugs with their ATC codes:
SELECT DISTINCT drg.name, map.code, map.vocabulary_level FROM drugs drg
  JOIN drug_mappings map ON map.drug_id = drg.id and map.vocabulary = 'ATC'
  ORDER BY drg.name;
Drugs are linked to clinical trials via the clinical_trial_interventions_drugs and clinical_trial_interventions tables. To get drugs with their clinical trial identifiers:
SELECT d.name, d.drugbank_id, t.identifier AS trial_identifier FROM clinical_trials t
  JOIN clinical_trial_interventions i        ON i.trial_id = t.identifier
  JOIN clinical_trial_interventions_drugs id ON id.intervention_id = i.id
  JOIN drugs d                               ON id.drug_id = d.id
  ORDER BY d.drugbank_id;
Drugs are linked to targets, enzymes, carriers, and transporters via the bonds table. To get drugs and their bonded entities:
SELECT d.name, b.type, be.name AS bond_name FROM drugs d
  JOIN bonds b         ON b.drug_id = d.id
  JOIN bio_entities be ON be.biodb_id = b.biodb_id
  ORDER BY d.name;
Drugs are linked to targets, enzymes, carriers, and transporters via the bonds table. Bonds are linked to polypeptides via the bio_entities and bio_entity_components tables. To get drugs, their bonded entities and the relevant polypeptides:
SELECT d.id, d.name, b.type,
  be.biodb_id, be.name, be.kind, be.organism,
  p.uniprot_id
FROM bonds b
JOIN drugs d ON d.id = b.drug_id
JOIN bio_entities be ON be.biodb_id = b.biodb_id
JOIN bio_entity_components bec ON bec.biodb_id = be.biodb_id AND bec.component_type = "Polypeptide"
JOIN polypeptides p ON p.uniprot_id = bec.component_id
ORDER by d.name, be.name;

Product Queries

When querying products, there are 4 boolean columns in particular that can be used to filter various kind of products:

approved:'1' = approved product,'0' = non-approved product
otc:'1' = over-the-counter product,'0' = prescription product
generic:'1' = generic product,'0' = branded/non-generic
mixture:'1' = compound/mixture product,'0' = single-drug product

The ingredients table creates a many-to-many relationship between products and drugs. Get all products and their drug ingredients:
SELECT * FROM products p
  JOIN ingredients i ON i.product_id = p.id
  JOIN drugs d       ON d.id = i.drug_id
  ORDER BY p.id;
Get all approved prescription products:
SELECT p.id, p.name FROM products p
  WHERE p.approved = '1' AND p.otc = '0' AND p.generic = '0' AND p.mixture = '0'
  ORDER BY p.name;
Get all approved generic prescription products:
SELECT p.id, p.name FROM products p
  WHERE p.approved = '1' AND p.otc = '0' AND p.generic = '1' AND p.mixture = '0'
  ORDER BY p.name;
Get all approved generic prescription products containing a given drug ingredient (e.g. Abacavir, drug_id = 1048):
SELECT p.id, p.name FROM products p
  JOIN ingredients drug_filter ON drug_filter.product_id = p.id
  WHERE drug_filter.drug_id = 1048 AND p.approved = '1' AND p.otc = '0' AND p.generic = '1' AND p.mixture = '0'
  ORDER BY p.name;
Get all brand mixtures and their ingredient drugs:
SELECT p.id, p.name, d.drugbank_id, d.name AS drug_name FROM products p
  JOIN ingredients i ON i.product_id = p.id
  JOIN drugs d       ON i.drug_id = d.id
  WHERE p.mixture = '1'
  ORDER BY p.name
Get all brand mixtures containing a given drug ingredient (e.g. Abacavir, drug_id = 1048):
SELECT p.id, p.name, d.drugbank_id, d.name AS drug_name FROM products p
  JOIN ingredients i           ON i.product_id = p.id
  JOIN drugs d                 ON i.drug_id = d.id
  JOIN ingredients drug_filter ON drug_filter.product_id = p.id
  WHERE p.mixture = '1' AND drug_filter.drug_id = 1048
  ORDER BY p.name;
Get a list of products with their dosage forms:
SELECT p.name, df.name AS form FROM products p
  JOIN dosage_forms_products dfp ON dfp.product_id = p.id
  JOIN dosage_forms df           ON dfp.dosage_form_id = df.id
  ORDER BY p.name;
Get a list of products with their dosage routes:
SELECT p.name, dr.name AS route FROM products p
  JOIN dosage_routes_products drp ON drp.product_id = p.id
  JOIN dosage_routes dr           ON drp.dosage_route_id = dr.id
  ORDER BY p.name;
Get a list of parent and child product concept pairs:
SELECT pc.title as parent_title, child.title AS child_title FROM product_concepts pc
  JOIN product_concept_children is_a ON is_a.parent_id = pc.id
  JOIN product_concepts child        ON child.id = is_a.child_id
  ORDER BY pc.title;

Structured Indication Queries

Each row in the structured_indications table represents a structured indication for a drug, referenced by the drug_id column. In addition to this row, each indication also includes one or more rows in the indication_attributes, indication_conditions, indication_drugs and indication_categories tables. Interpreting a indication requires bringing together all relevant data from these tables. Plese refer to the Structured indications for more details.

Get basic indication/condition information and indication main drug for a given condition. Additional drugs may be required for combination indications; these can be found by joining with the indication_drugs/indication_categories tables.
SELECT d.name AS drug_name, d.drugbank_id, i.kind, c.title AS condition_name
FROM structured_indications i
JOIN drugs d ON d.id = i.drug_id
JOIN indication_conditions ic
  ON ic.indication_id = i.id
  AND ic.relationship = 'for_condition'
JOIN conditions c ON c.preferred_term_id = ic.condition_id
JOIN accession_numbers an
  ON an.record_id = c.id
  AND an.record_type = 'Condition'
WHERE an.number = 'DBCOND0029752'
LIMIT 30;
Get all indications directly involving a drug. In this query, the drug is identified via its row id (331 = Metformin) for simplicity.
Note that there are two direct relationships between indication and drug - the drug_id column in the structured_indications table, and the indication_drugs table.
This query uses both relationships and reports the type of connection between the indication and the drug.
An outer join is used for the indication_drugs table, and the joined rows are filtered by drug id and relationship. This way, only rows directly relevant to the target drug will be found.
The conditions table is joined via the indication_conditions table, filtering on the relationship between condition and indication.
SELECT DISTINCT i.kind, c.title AS condition_name, COALESCE(i_drugs.relationship, "main drug") AS indication_relationship
FROM structured_indications i
LEFT OUTER JOIN indication_drugs i_drugs
  ON i_drugs.indication_id = i.id
  AND i_drugs.drug_id = 331
  AND i_drugs.relationship = "combination"
JOIN indication_conditions ic ON ic.indication_id = i.id AND ic.relationship = "for_condition"
JOIN conditions c ON c.id = ic.condition_id
WHERE i.drug_id = 331 OR i_drugs.drug_id = 331;

Structured Contraindication Queries

Each row in the structured_contraindications table represents a structured contraindication for a drug, referenced by the drug_id column. In addition to this row, each contraindication also includes one or more rows in the contraindication_attributes, contraindication_conditions, contraindication_drugs and contraindication_categories tables. Interpreting a contraindication requires bringing together all relevant data from these tables. Plese refer to the Structured Contraindications for more details.

Get all contraindicated conditions from structured contraindications for a given relationship called 'patient_condition' and a given drug (e.g. Metformin, drug_id = 331):
SELECT sc.id, c.title AS condition_title, cc.relationship, d.drugbank_id, d.name FROM structured_contraindications sc
  JOIN contraindication_conditions cc ON sc.id = cc.contraindication_id
  JOIN conditions c ON c.id = cc.condition_id
  JOIN drugs d ON d.id = sc.drug_id
  WHERE sc.drug_id = 331 AND cc.relationship = 'patient_condition';
Get all contraindicated attributes from structured contraindications for a given drug (e.g. Metformin, drug_id = 331). The contraindication attributes provide information which is essential to the correct interpretation of their related contraindications. :
SELECT sc.id, ca.relationship, ca.value, d.drugbank_id, d.name FROM structured_contraindications sc
  JOIN contraindication_attributes ca ON sc.id = ca.contraindication_id
  JOIN drugs d ON d.id = sc.drug_id
  WHERE sc.drug_id = 331;

Structured Adverse Effect Queries

Each row in the structured_adverse effects table represents a structured adverse effect for a drug, referenced by the drug_id column. In addition to this row, each adverse effect also includes one or more rows in the adverse_effect_attributes, adverse_effect_conditions, adverse_effect_incidences, adverse_effect_drugs and adverse_effect_categories tables. Interpreting an adverse effect requires bringing together all relevant data from these tables. Please refer to the Structured Adverse Effects documentation for more details.

Get all adverse effect conditions from structured adverse effects for a given drug (e.g. Metformin, drug_id = 331):
SELECT sa.id, c.title AS condition_title, ae.relationship AS condition_relationship, d.drugbank_id, d.name FROM structured_adverse_effects sa
  JOIN adverse_effect_conditions ae ON sa.id = ae.adverse_effect_id
  JOIN conditions c ON c.id = ae.condition_id
  JOIN drugs d ON d.id = sa.drug_id
  WHERE sa.drug_id = 331 AND ae.relationship = 'effect';
Get all experimental adverse effect percentages and their conditions from structured adverse effects for a given drug (e.g. Metformin, drug_id = 331):
SELECT sa.id, c.title AS condition_title, ac.relationship AS condition_relationship, ai.percent, d.drugbank_id, d.name FROM structured_adverse_effects sa
  LEFT OUTER JOIN adverse_effect_incidences ai ON sa.id = ai.adverse_effect_id AND ai.kind = 'experimental'
  JOIN adverse_effect_conditions ac ON sa.id = ac.adverse_effect_id
  JOIN conditions c ON ac.condition_id = c.id
  JOIN drugs d ON d.id = sa.drug_id
  WHERE sa.drug_id = 331 AND ac.relationship = 'effect'
  ORDER BY ai.percent;

Other Queries

Get all conditions and their synonyms:
SELECT c.*, s.* FROM conditions c
  JOIN conditions s          ON s.preferred_term_id = c.id
  ORDER BY c.drugbank_id;