April 1, 2008 at 5:37 am
I have a shipmentline table that stores lines to be shipped.
I have a shipmentLineStatus table that stores status data for the shipment line.
There can be many shipmentStatus lines for each shipment line.
I have to write a query to return shipmentLineID's that dont have a row that has a status of SHI ( shipped.
below is a mockup of the table and some data. Ive spent about 2 hours on this and cant figure it out. any help would be greatly appriciated.
Thanks all
create table #testSLIS
(
SLISID int,
ShipmentLineItemID int,
statuscode varchar(3)
)
insert into #testSLIS
values(1,100,'SHI')
insert into #testSLIS
values(2,100,'PRO')
insert into #testSLIS
values(3,200,'PRO')
insert into #testSLIS
values(4,200,'REJ')
insert into #testSLIS
values(5,300,'SHI')
insert into #testSLIS
values(6,300,'PRO')
insert into #testSLIS
values(7,300,'REJ')
insert into #testSLIS
values(8,400,'REJ')
insert into #testSLIS
values(9,400,'PRO')
insert into #testSLIS
values(10,500,'REJ')
--need to return shipmentlineitemid 200,400 ,500 as they dont contain a row with a status of SHI
April 1, 2008 at 5:46 am
SELECT
...
FROM shipmentline sl
LEFT JOIN shipmentLineStatus sts ON sl.ShipmentLineItemID = sts.ShipmentLineItemID
AND sts.statuscode = 'SHI'
WHERE sts.ShipmentLineItemID IS NULL --does not exist
not exists could also be used, as well as not in (which I do not reccomend)
April 1, 2008 at 5:54 am
works perfectly. thanks for the help. with the help here i am getting better, honest! some queries just get me.
April 1, 2008 at 6:47 am
just out of curosity, is this possible using just the one table ( see DDL code above for table info).
April 1, 2008 at 6:54 am
sure.
you just have to join to itself
SELECT DISTINCT
tmp.ShipmentLineItemID
FROM #testSLIS tmp
LEFT JOIN #testSLIS sts ON tmp.ShipmentLineItemID = sts.ShipmentLineItemID
AND sts.statuscode = 'SHI'
WHERE tmp.statuscode <> 'SHI'
AND sts.ShipmentLineItemID IS NULL --does not exist
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply