SQL Main table with criteria + inner join from a secondary table

[Debora1]

Hello!

I am using SQL trying to view the nct_id column that matches 3  criteria and i have 2 tables that share the nct_id.

1. 2 criterias are within the table studies
2. Last criteria is in table interventions.

I am not sure how to connect first two criterias to the inner join, do you have any clue?

select studies.nct_id 
	from studies where study_type='Interventional' and overall_status='Completed'
	inner join interventions 
		on studies.nct_id=interventions.nct_id where intervention_type='Drug';	

ERROR: syntax error at or near "inner" LINE 20: inner join interventions on studies.nct_id=interventions.n... ^

Many thanks!

[sustagens · Accepted solution]

You only need one "where" after your "from". You can specify what table to apply your filter by using its alias (or table name in your code)

Try this

select studies.nct_id
from studies 
inner join interventions 
on studies.nct_id=interventions.nct_id

where studies.study_type = 'Interventional' and studies.overall_status = 'Completed'
and intervention.intervention_type = 'Drug'

[Debora1]

Thaaaanks so much! Thaaat now makes a lot of sense!

[ChrisNZ]

Or, if you want use SAS data set options:

select studies.nct_id 
  from studies(where=(study_type='Interventional' and overall_status='Completed'))
         inner join
       interventions (where=(intervention_type='Drug'))
	     on studies.nct_id = interventions.nct_id 

[Debora1]

Thanks so much Chris! Thats a lot simpler than what i was doing!

[Kurt_Bremser]

proc sql;
create table want as
  select studies.nct_id 
  from studies st, interventions in
  where
    st.nct_id = in.nct_id
    and st.study_type = 'Interventional' and st.overall_status = 'Completed'
	and in.intervention_type = 'Drug'
;	
quit;

[Debora1]

Hello Kurt!
Thanks, that was also something I was looking to do