Question:
I am a SQL beginner and I am trying to write this query. I have a table with four columns:
ID STATUS DESCRIPTION CURRDATE1 open Test1 6/1/001 open Test1 6/2/001 close Test1 6/3/002 open Test2 6/2/002 open Test2 6/4/003 open Test3 6/4/00
I would like to show only the ID when status = ‘open’; once status = ‘close’ I do not want to show the ID.
Answer:
Given the data in your test table, you can write a subquery that first extracts the closed items and then does a query of open items, like this:
SELECT * FROM TestTableWHERE ID NOT IN (SELECT ID FROM Testtable WHERE Status = 'close')
The query returns this resultset from the given data (notice that no records for ID 1 are in the resultset because the ID has a record with a status of ‘close’):
ID Status Description CurrDate -- ------ ----------- -----------------------2 open Test2 2000-06-02 00:00:00.0002 open Test2 2000-06-04 00:00:00.0003 open Test3 2000-06-04 00:00:00.000
