devxlogo

Writing a Query Showing ID Status

Writing a Query Showing ID Status

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

devx-admin

Share the Post: