order query joins to first order detail
-
We have a typical order table that relates to orderDetails. I'm trying to write a view of the orders table that joins to orderDetails. However, I only want to return 1 row for each order, so if there are multiple rows in orderDetails I only want the first one, i.e. the firt line item. I tried something like
select o.orderid, od1.productID
from orders o
LEFT JOIN (SELECT top 1 od.orderID, min(orderDetailID) as minOrderDetailID, od.productID
FROM orderDetails od
WHERE mediaID = 0
GROUP BY od.orderID, od.orderdetailid, od.productID)
as od1 ON o.orderID = od1.orderID
but that doesn't give the desired result. Ideally I'd like to keep this as a view. Any ideas?
-
Something like this...
declare @orders table (orderid int)
insert @orders
select 1
union all select 2
union all select 3
declare @orderDetails table (orderDetailID int, orderid int, productID int)
insert @orderDetails
select 1, 1, 13
union all select 2, 1, 15
union all select 3, 1, 17
union all select 4, 2, 17
union all select 5, 2, 13
select o.orderid, firstOrderDetail.productID
from @orders o
LEFT JOIN (
select od.* from @orderDetails od
inner join (select orderid, min(orderDetailID) as orderDetailID from @orderDetails group by orderid
) firstOrderDetailIDs
on od.orderDetailID = firstOrderDetailIDs.orderDetailID
) firstOrderDetail
on o.orderid = firstOrderDetail.orderid
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Soon after I posted I realized having orderdetailid and productid in the group by was causing the multiple rows. I made the changes like you describe and it works now. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply