Strange Query, Show only shipped status,

[josephjoe]

Current Data

ID Part Status
--- ---- ------
5 Glue Received
7 Paint Shipped
5 Glue Shipped
3 Hammer Received
7 Paint Received


Query I am trying
ID Part Status
--- ---- ------
3 Hammer Received


I am trying to run a query that will show all the Id's that have not shipped,

I can't run 'where status <> 'shipped' cuz it will show the received status of the one that have shipped.

I think I have been thinking about it too much and can not think outside the box. Any help would be great.

 

[monksnake]

Here, try this:

declare @table table(ID int, Part char(10), Status char(10))
insert into @table values (5, 'Glue', 'Received')
insert into @table values (7, 'Paint', 'Shipped')
insert into @table values (5, 'Glue', 'Shipped')
insert into @table values (3, 'Hammer', 'Received')
insert into @table values (7, 'Paint', 'Received')

select ID, Part, Status from @table where ID not in (Select ID from @table where Status = 'Shipped')

<.