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.
| Author |
Topic |
|
myst_ukon
Starting Member
2 Posts |
 Posted - 2014-08-23 : 07:42:32
|
| 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) UsedFROM Order o, EA xa, ClassDef cdefWHERE (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 |
|
|
|
|
|
|
|
outer join conversion from 2000 not working the sa