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
 Site Related Forums
 Article Discussion
 outer join conversion from 2000 not working the sa
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

myst_ukon
Starting Member

USA
2 Posts

Posted - 08/23/2014 :  07:42:32  Show Profile  Reply with Quote
can someone help me to convert these joins. I have tried and tried, but cannot get the same results that this query produces when I try to convert to ansi OUTER joins: basically, I need a row for every order that is in Order and/or EA or and one row for each of the 3 returned by cdef = 1 (there are 3 values so if there is an order in Order with no matching OrderNumber in EA, I should get 3 rows back for each value in cdef (alt1, alt2, ex)

SELECT
o.OrderNumber, o.companyname Company, cdef.shortname RegClass,
isnull(xa.Allotment,0) Allotment,
IsNull((select sum(qty) from tab_item where OrderNumber = xa.OrderNumber and ItemCode = cdef.Purchase_Allotment_Item and ItemStatus = 'A'),0) Purchased,
IsNull((select count(*) from tab_reg where regclass = xa.regclass and OrderNumber = xa.OrderNumber),0) Used

FROM
Order o,
EA xa,
ClassDef cdef

WHERE
(cdef.exhibitor = 1)
and xa.regclass =* cdef.shortname
and o.ordernumber *= xa.ordernumber
and o.ordernumber <> 30000
and o.OrderType = 1

Order By
o.companyname
  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.03 seconds. Powered By: Snitz Forums 2000