topic Re: SQL Main table with criteria + inner join from a secondary table in SAS Programming

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

Debora1 — Fri, 24 Apr 2020 01:08:01 GMT

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.

2 criterias are within the table studies
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!

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

sustagens — Fri, 24 Apr 2020 01:51:13 GMT

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'

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

ChrisNZ — Fri, 24 Apr 2020 04:22:26 GMT

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

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

Kurt_Bremser — Fri, 24 Apr 2020 09:57:10 GMT

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;

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

Debora1 — Fri, 24 Apr 2020 15:09:32 GMT

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

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

Debora1 — Fri, 24 Apr 2020 15:11:22 GMT

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

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

Debora1 — Fri, 24 Apr 2020 15:13:21 GMT

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