A question in SQL Query
-
Hi, My query looks like this select distinct lh.id, od.storename FROM LPNHeader lh LEFT OUTER join LPNDetail ld on lh.Id = ld.LPNHdrId LEFT OUTER JOIN OrderDetail od on ld.OrderDtlId = od.Id INNER JOIN OrderShipmentLPN OSL ON LH.id = OSL.LPNHdrId INNER JOIN ShipmentHeader sh on sh.id = osl.shipmentHdrId WHERE sh.Id = 25 and the results are 346 SN1 347 SN1 347 SN2 348 SN2 348 SN3 349 NULL I want to finetune this query to return distinct lh.id's, like given below 346 SN1 347 SN1 348 SN2 349 NULL Tried distinct, group etc. I think that I am missing something, anybody any ideas. Thanks
-
Hi, My query looks like this select distinct lh.id, od.storename FROM LPNHeader lh LEFT OUTER join LPNDetail ld on lh.Id = ld.LPNHdrId LEFT OUTER JOIN OrderDetail od on ld.OrderDtlId = od.Id INNER JOIN OrderShipmentLPN OSL ON LH.id = OSL.LPNHdrId INNER JOIN ShipmentHeader sh on sh.id = osl.shipmentHdrId WHERE sh.Id = 25 and the results are 346 SN1 347 SN1 347 SN2 348 SN2 348 SN3 349 NULL I want to finetune this query to return distinct lh.id's, like given below 346 SN1 347 SN1 348 SN2 349 NULL Tried distinct, group etc. I think that I am missing something, anybody any ideas. Thanks
Use a subquery to return the first storename for each id:
select distinct LPNHeader.id, Storename = ( SELECT TOP 1 oh.storename FROM LPNDetail ld LEFT OUTER JOIN OrderDetail od on ld.OrderDtlId = od.Id INNER JOIN OrderShipmentLPN OSL ON LH.id = OSL.LPNHdrId INNER JOIN ShipmentHeader sh on sh.id = osl.shipmentHdrId WHERE LPNHeader.Id = ld.LPNHdrId AND sh.Id = 25 ) FROM LPNHeader