Is it possible to use select within join condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is it possible to use select within join condition

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-27 : 16:53:54

I have the following two select queries, i want to use teh second second in the first query join is it possible:

select plan_name from health_plan where health_plan.health_plan_id = E_Plan_R.health_plan_id and 
health_plan.active_ind=1


i want to use this query to get health_plan_id which is used in teh above query after equalto sign, is it possible to directly use it there in the above query to make as one single instead of two.


select health_plan_id from encntr_plan_reltn where enc.encid= encntr_plan_reltn.encid and priority_seq=1 and active_ind=1
and beg_eff_dt > = sysdate();

I am planning to use the above query's as inquery/subquery within a big sql query block where all joins etc already defined and working just want to add the above one.

Thank you very much for the help.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-27 : 17:42:43

OK got it:

(select plan_name from health_plan where health_plan_id =(select health_plan_id from encntr_plan_reltn where enc.encntr_id=
encntr_plan_reltn.encntr_id and priority_seq=1 and active_ind=1 and BEG_EFFECTIVE_DT_TM >= sysdate() and rownum < 2) and

health_plan.active_ind=1 and rownum < 2)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 18:45:43

This might be better

select plan_name
from health_plan hp
where exists

(select *
from encntr_plan_reltn r
where enc.encntr_id= encntr_plan_reltn.encntr_id
and priority_seq=1 and active_ind=1 and BEG_EFFECTIVE_DT_TM >= sysdate() and rownum < 2) and
health_plan.active_ind=1 and rownum < 2
and hp.health_plan_id = r.health_plan_id

or even just this

select hp.plan_name
from health_plan hp
inner join encntr_plan_reltn r
on hp.health_plan_id = r.health_plan_id
where enc.encntr_id= encntr_plan_reltn.encntr_id
and r.priority_seq=1 and r.active_ind=1 and r.BEG_EFFECTIVE_DT_TM >= sysdate() and r.rownum < 2) and
health_plan.active_ind=1 and rownum < 2
)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources