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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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