May 20, 2014 at 9:08 am
In t-sql 2208 r2, I have sql that looks like the following:
select a.custname, i.item, i.amount, so.specialitem, so.specialamount
from customer a
inner join inventory i on a.custid = i.custid
left outer join on specialorder so on so.custid = so.custid
The problem is with the left outer join on the specialorder table.
Only about 50% of the time is there a special order. In rare cases,
there are 2 or more specialorder items that a customer picks.
My problem is that the user only wants one spcialorder item to be reported on.
The user does not care which specialorder item is appearing, they just one one
item selected.
Thus can you tell me how to change the sql listed above so that I can do
what my user wants to see?
May 20, 2014 at 9:17 am
Without knowing a bit more, this is the best I can do to give you a starting place. This puts a "row number" on each special order, then joins that to the other tables where [row] = 1. Make sure you understand the code before you use it!
WITH specialOrders () {
SELECT so.CustID, so.SpecialItem, sp.SpecialAmount,
row = ROW_NUMBER() OVER (ORDER BY so.CustID)
FROM dbo.SpecialOrder so
}
select a.custname, i.item, i.amount, so.specialitem, so.specialamount
from dbo.customer a inner join
dbo.inventory i on a.custid = i.custid left outer join
(SELECT * FROM specialOrders WHERE [Row] = 1) AS so on so.custid = so.custid
wendy elizabeth (5/20/2014)
In t-sql 2208 r2, I have sql that looks like the following:
select a.custname, i.item, i.amount, so.specialitem, so.specialamount
from customer a
inner join inventory i on a.custid = i.custid
left outer join on specialorder so on so.custid = so.custid
The problem is with the left outer join on the specialorder table.
Only about 50% of the time is there a special order. In rare cases,
there are 2 or more specialorder items that a customer picks.
My problem is that the user only wants one spcialorder item to be reported on.
The user does not care which specialorder item is appearing, they just one one
item selected.
Thus can you tell me how to change the sql listed above so that I can do
what my user wants to see?
May 20, 2014 at 9:52 am
You don't even need the ROW_NUMBER if you don't care about the order. You could use the APPLY operator.
SELECT a.custname, i.item, i.amount, s.specialitem, s.specialamount
FROM customer a
INNER JOIN inventory i on a.custid = i.custid
OUTER APPLY (SELECT TOP 1 so.specialitem, so.specialamount
FROM specialorder so
WHERE a.custid = so.custid) s
EDIT: I forgot to mention that your code had a wrong JOIN condition that would cause a CROSS JOIN (so.custid = so.custid).
May 20, 2014 at 10:54 am
what does the 'outer apply' do in the sql you just listed?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply