query.sql

SELECT
  s.nct_id,
  s.brief_title,
  s.phase,
  s.enrollment,
  s.start_date,
  s.primary_completion_date,
  s.last_update_submitted_date,
  sp.name,
  sp.agency_class,
  i.name
FROM studies s

-- Find who is sponsoring the trial
JOIN sponsors sp
  ON s.nct_id = sp.nct_id

-- Get details on the drug intervention
JOIN interventions i
  ON s.nct_id = i.nct_id

-- Add in keyword searches
JOIN detailed_descriptions dd
  ON s.nct_id = dd.nct_id

-- Filter based on various criteria
WHERE sp.lead_or_collaborator = 'lead'
  AND s.phase NOT LIKE '%1%'
  AND s.phase NOT LIKE '%2%'
  AND s.phase != 'N/A'
  AND s.phase IS NOT NULL
  AND i.intervention_type = 'Drug'
  AND sp.agency_class = 'Industry'
  
  AND (
        dd.description LIKE '%KEYWORD%'
    
        -- Just duplicate this line for each keyword
        OR dd.description LIKE '%KEYWORD2%'
      )
ORDER BY
  s.completion_date desc,
  s.last_update_submitted_date desc