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
 General SQL Server Forums
 New to SQL Server Programming
 Not use Outer Apply		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petronas
Posting Yak Master

117 Posts
Posted - 06/01/2012 :  11:26:46  Show Profile  Reply with Quote
Hi All,

Is there a way to write the below query without using Outer Apply?

select b.order_id ,b.billing_id,transaction_type,marketing_id, b.billing_date, order_date
, activestartdate, activeenddate

FROM billing b
Outer Apply

( SELECT marketing_id, order_date
, activestartdate, sactiveenddate
from
orders o
inner join orderdetails do
on o.dimorder_id = do.dimorder_id
where
o.order_id = b.order_id
and b.billing_date between do.activestartdate and do.activeenddate
) o
order by order_id

Thanks for your help,
Petronas

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 06/01/2012 :  11:32:07  Show Profile  Reply with Quote
yep use left join

select b.order_id ,b.billing_id,transaction_type,marketing_id, b.billing_date, order_date
, activestartdate, activeenddate

FROM billing b 
LEFT JOIN
( SELECT marketing_id, order_date
, activestartdate, sactiveenddate
from 
orders o 
inner join orderdetails do
on o.dimorder_id = do.dimorder_id
) o
on o.order_id = b.order_id 
and b.billing_date between do.activestartdate and do.activeenddate
order by b.order_id




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Petronas
Posting Yak Master

117 Posts
Posted - 06/01/2012 :  13:19:21  Show Profile  Reply with Quote
Thanks Visakh16, it worked perfectly with the Left Join. Appreciate your help on this. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 06/01/2012 :  23:40:37  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
311 Posts
Posted - 06/02/2012 :  11:24:52  Show Profile  Reply with Quote 



For more Information :

Outer apply is similar to Left Outer Join 

cross apply is similar to Inner join 

Only difference is we can join a table with function .


Vijay is here to learn something from you guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 06/02/2012 :  13:55:11  Show Profile  Reply with Quote
its not exactly a join like more of correlated subquery. Its actually lot more than a join and in many scenarios will provide much better execution plan and performance than join especially when query involves some kind of correlated logic.most occasions apply operator can be used instead of join to yield the same execution plan but there are scenarios where it has its upper hand over joins especially when logic involves some kind of correlation. the same logic if implemented using join would result in RBAR problem and can hurt performance




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.06 seconds. Powered By: Snitz Forums 2000