Toss out all rows for an ID on specified value 1

[emccormick]

Hi

I have a table structured like this:

Item Status
-----------------
SN1 READY
SN1 INPROCESS
SN1 FINISHED
SN2 READY
SN3 INPROCESS
SN4 INPROCESS
SN4 FINISHED

I need to be able to isolate the Items which haven't been FINISHED. In other words, if the Item has a status of FINISHED anywhere in the table, don't return the Item at all.

In the above example, the correct result would be SN2, and SN3.

Thx!

 

[nomadicalloy]

Are you looking for something like:

select distinct Item from mytable where Status not in 'FINISHED'

thanks


This is Signature

http://www.xpherion.com/

 

[cfStarlight]

Try

SELECT DISTINCT t1.Item
FROM YourTable t1 LEFT JOIN
(
SELECT item
FROM YourTable
WHERE status = 'Finished'
)
AS t2 ON t1.Item = t2.Item
WHERE t2.item IS NULL

-- ... OR

SELECT DISTINCT Item
FROM YourTable t1
WHERE NOT EXISTS (
SELECT 0
FROM YourTable t2
WHERE t2.Item = t1.Item
AND t2.Status = 'Finished'
)

 

[emccormick]

thx all.

for some reason, i couldn't post replies from work yesterday.

i ended up solving it (more or less) with a CTE (I'm in SQL 2005):

--the CTE definistion
with finished
as
(
select Item
from myTable
where Status = 'FINISHED'
)

--the accessor query
select Item
from myTable
where Item not in (select * from finished)

This yielded the exact list of Items I needed.

But then I decided I wanted just a count of the Items that met the same criteria, so in the accessor query, I did this:

select count(Item)
from myTable
where Item not in (select * from finished)

Instead of a single aggregate result, I got the same number of results as before, only each was some random accumulated value.

I tried select count(distinct(Item)) and got back a list of 1s where the length of the list was equal to the number of items.

Any idea how to run an aggregate in a CTE's accessor query?

 

[cfStarlight]

Sorry, no. I'm using SQL 2000.

 

[emccormick]

in any case, what i didn't show in my example was that i had some grouping in the sql query, and that was causing the problem.

also, my company's websense filtering doesn't seem to permit webform submissions to tech-help sites, hence the total lack of response from me.