Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 20, 2000

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  CURRDATE
1   open    Test1        6/1/00
1   open    Test1        6/2/00
1   close   Test1        6/3/00
2   open    Test2        6/2/00
2   open    Test2        6/4/00
3   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 TestTable
WHERE 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.000
2  open   Test2       2000-06-04 00:00:00.000
3  open   Test3       2000-06-04 00:00:00.000
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date