June 25, 2008 at 1:19 pm
I have a query where I am trying to do multiple joins to the same table. If one of the rows in the ordercomponentvalues table doesn't exist, no info is returned(not all orders have a 3rd party discount). I thought my left outer joins would take care of this. Any help would be appreciated.
SELECT billingcarriers.description, orders.orderid, orders.orderdate, orders.patientid,
sum(ocv1.amount) retail, sum(ocv2.amount) cost,
sum(ocv3.amount * -1) exp_reim, sum(ocv4.amount * -1) tp_disc,
sum(ocv5.amount * -1) ins_wo, isnull(sum(ocv6.amount * -1),0) pat_wo
FROM Orders INNER JOIN
OrderProducts ON Orders.OrderID = OrderProducts.OrderID INNER JOIN
OrderComponents ON OrderProducts.ProductID = OrderComponents.ProductID LEFT OUTER JOIN
OrderComponentValues ocv1 ON OrderComponents.ComponentID = ocv1.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv2 ON OrderComponents.ComponentID = ocv2.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv3 ON OrderComponents.ComponentID = ocv3.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv4 ON OrderComponents.ComponentID = ocv4.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv5 ON OrderComponents.ComponentID = ocv5.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv6 ON OrderComponents.ComponentID = ocv6.ComponentID INNER JOIN
OrderPlans ON Orders.OrderID = OrderPlans.OrderID INNER JOIN
Billings ON OrderPlans.BillingID = Billings.BillingID INNER JOIN
BillingPlans ON Billings.PlanID = BillingPlans.PlanID INNER JOIN
BillingCarriers ON BillingPlans.CarrierID = BillingCarriers.CarrierID
WHERE ocv1.valuetypeid = 1 -- retail
AND ocv2.ValueTypeID = 13 -- cost
AND ocv3.ValueTypeID = 3 -- expected ins payment
AND ocv4.ValueTypeID = 4 -- 3rd party discount
AND ocv5.ValueTypeID = 10 -- ins writeoff
AND ocv6.ValueTypeID = 15 -- patient writeoff
AND Billings.statuscodeid = 4
AND OrderProducts.producttypeid in (6, 7) --frames and frame parts
AND orders.orderid = 10050910
GROUP BY billingcarriers.description, orders.orderid, orders.orderdate, orders.patientid
June 25, 2008 at 1:28 pm
SELECT billingcarriers.description, orders.orderid, orders.orderdate, orders.patientid,
sum(ocv1.amount) retail, sum(ocv1.amount) cost,
sum(ocv1.amount * -1) exp_reim, sum(ocv1.amount * -1) tp_disc,
sum(ocv1.amount * -1) ins_wo, isnull(sum(ocv1.amount * -1),0) pat_wo
FROM Orders INNER JOIN
OrderProducts ON Orders.OrderID = OrderProducts.OrderID
INNER JOIN
OrderComponents ON OrderProducts.ProductID = OrderComponents.ProductID
LEFT OUTER JOIN
OrderComponentValues ocv1 ON OrderComponents.ComponentID = ocv1.ComponentID
INNER JOIN
OrderPlans ON Orders.OrderID = OrderPlans.OrderID INNER JOIN
Billings ON OrderPlans.BillingID = Billings.BillingID INNER JOIN
BillingPlans ON Billings.PlanID = BillingPlans.PlanID INNER JOIN
BillingCarriers ON BillingPlans.CarrierID = BillingCarriers.CarrierID
WHERE ocv1.valuetypeid in (1,13,3,4,10,15)
AND Billings.statuscodeid = 4
AND OrderProducts.producttypeid in (6, 7) --frames and frame parts
AND orders.orderid = 10050910
GROUP BY billingcarriers.description, orders.orderid, orders.orderdate, orders.patientid
June 25, 2008 at 1:36 pm
Bryan Mischler (6/25/2008)
I have a query where I am trying to do multiple joins to the same table. If one of the rows in the ordercomponentvalues table doesn't exist, no info is returned(not all orders have a 3rd party discount). I thought my left outer joins would take care of this. Any help would be appreciated.SELECT billingcarriers.description, orders.orderid, orders.orderdate, orders.patientid,
sum(ocv1.amount) retail, sum(ocv2.amount) cost,
sum(ocv3.amount * -1) exp_reim, sum(ocv4.amount * -1) tp_disc,
sum(ocv5.amount * -1) ins_wo, isnull(sum(ocv6.amount * -1),0) pat_wo
FROM Orders INNER JOIN
OrderProducts ON Orders.OrderID = OrderProducts.OrderID INNER JOIN
OrderComponents ON OrderProducts.ProductID = OrderComponents.ProductID LEFT OUTER JOIN
OrderComponentValues ocv1 ON OrderComponents.ComponentID = ocv1.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv2 ON OrderComponents.ComponentID = ocv2.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv3 ON OrderComponents.ComponentID = ocv3.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv4 ON OrderComponents.ComponentID = ocv4.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv5 ON OrderComponents.ComponentID = ocv5.ComponentID LEFT OUTER JOIN
OrderComponentValues ocv6 ON OrderComponents.ComponentID = ocv6.ComponentID INNER JOIN
OrderPlans ON Orders.OrderID = OrderPlans.OrderID INNER JOIN
Billings ON OrderPlans.BillingID = Billings.BillingID INNER JOIN
BillingPlans ON Billings.PlanID = BillingPlans.PlanID INNER JOIN
BillingCarriers ON BillingPlans.CarrierID = BillingCarriers.CarrierID
WHERE ocv1.valuetypeid = 1 -- retail
AND ocv2.ValueTypeID = 13 -- cost
AND ocv3.ValueTypeID = 3 -- expected ins payment
AND ocv4.ValueTypeID = 4 -- 3rd party discount
AND ocv5.ValueTypeID = 10 -- ins writeoff
AND ocv6.ValueTypeID = 15 -- patient writeoff
AND Billings.statuscodeid = 4
AND OrderProducts.producttypeid in (6, 7) --frames and frame parts
AND orders.orderid = 10050910
GROUP BY billingcarriers.description, orders.orderid, orders.orderdate, orders.patientid
By including the ocv#.valuetypeid = # check in the where clause - you are not getting an OUTER JOIN. Basically, when a row doesn't match in ocv6 the value for ocv6.ValueTypeID is NULL. Since NULL is not equal to anything - the row is excluded.
Move each one of those checks into the ON clause of the join and it should work.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 25, 2008 at 1:40 pm
The Where clause is essentially changing them back from Outer to Inner, because if ocv3.valuetypeid = 13 does not exist, the Where clause will eliminate the whole row.
Try this, see if it fixes what you're looking for:
SELECT billingcarriers.description,
orders.orderid,
orders.orderdate,
orders.patientid,
sum(ocv1.amount) retail,
sum(ocv2.amount) cost,
sum(ocv3.amount * -1) exp_reim,
sum(ocv4.amount * -1) tp_disc,
sum(ocv5.amount * -1) ins_wo,
isnull(sum(ocv6.amount * -1),0) pat_wo
FROM Orders
INNER JOIN OrderPlans
ON Orders.OrderID = OrderPlans.OrderID
INNER JOIN Billings
ON OrderPlans.BillingID = Billings.BillingID
INNER JOIN BillingPlans
ON Billings.PlanID = BillingPlans.PlanID
INNER JOIN BillingCarriers
ON BillingPlans.CarrierID = BillingCarriers.CarrierID
INNER JOIN OrderProducts
ON Orders.OrderID = OrderProducts.OrderID
INNER JOIN OrderComponents
ON OrderProducts.ProductID = OrderComponents.ProductID
LEFT OUTER JOIN OrderComponentValues ocv1
ON OrderComponents.ComponentID = ocv1.ComponentID
AND ocv1.valuetypeid = 1 -- retail
LEFT OUTER JOIN OrderComponentValues ocv2
ON OrderComponents.ComponentID = ocv2.ComponentID
AND ocv2.ValueTypeID = 13 -- cost
LEFT OUTER JOIN OrderComponentValues ocv3
ON OrderComponents.ComponentID = ocv3.ComponentID
AND ocv3.ValueTypeID = 3 -- expected ins payment
LEFT OUTER JOIN OrderComponentValues ocv4
ON OrderComponents.ComponentID = ocv4.ComponentID
AND ocv4.ValueTypeID = 4 -- 3rd party discount
LEFT OUTER JOIN OrderComponentValues ocv5
ON OrderComponents.ComponentID = ocv5.ComponentID
AND ocv5.ValueTypeID = 10 -- ins writeoff
LEFT OUTER JOIN OrderComponentValues ocv6
ON OrderComponents.ComponentID = ocv6.ComponentID
AND ocv6.ValueTypeID = 15 -- patient writeoff
WHERE Billings.statuscodeid = 4
AND OrderProducts.producttypeid in (6, 7) --frames and frame parts
AND orders.orderid = 10050910
GROUP BY billingcarriers.description, orders.orderid,
orders.orderdate, orders.patientid
Edit: I laid out the query in a fashion I find much more readable. If you need to change the layout back, go ahead. If, on the other hand, you are ending up with the layout you had because you're using the GUI query builder, be warned that queries like this one can be broken by the editor without you knowing it. I recommend against using it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 1:49 pm
Thanks Jeff and GSquared! It works great now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply