SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Is it possible to use select within join condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

533 Posts

Posted - 11/27/2012 :  16:53:54  Show Profile  Reply with Quote
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

533 Posts

Posted - 11/27/2012 :  17:42:43  Show Profile  Reply with Quote
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)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  18:45:43  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000