CODEWITHSUNDEEP
sql
Usman
Usman

Reputation: 23

How to select data from table based on a condition on its column

I am totally new to sql, I have a table like:

enter image description here

Using SQL, I want to select those rows that have no "INPROGRESS" state for the same LOCKID. For example, in above table query should return rows with id 4, 5, 6. Thanks.

Upvotes: 0

Views: 882

Answers (2)

David Hammond
David Hammond

Reputation: 3306

You can use the NOT IN statement to exclude rows that have an 'INPROGRESS'. E.g.: 

SELECT  *
FROM    tbl
WHERE   LOCKID NOT IN ( SELECT  LOCKID
                        FROM    tbl
                        WHERE   STATE = 'INPROGRESS' )

You can also use NOT EXISTS to do the same thing

SELECT  *
FROM    tbl t1
WHERE   NOT EXISTS ( SELECT *
                     FROM   tbl t2
                     WHERE  t2.STATE = 'INPROGRESS'
                            AND t1.LOCKID = t2.LOCKID )

Sometimes one or the other construct will perform better, but in most cases, in my experience, they are pretty much equivalent.

Upvotes: 2

xQbert
xQbert

Reputation: 35323

Return all records from table (aliased "A") where state is is not inprogress provided that the lockID is not found in a set of data with a state of inprogress.

This uses a concept of a correlated query tying the inner query to outer. the select 1 in the subquery is throw away (but since the compiler requires a select to return a value even though we don't use it) since we only care about the LOCKID's matching.

SELECT *
FROM TABLE A
WHERE A.STATE <>  'INPROGRESS'
 AND NOT EXISTS (SELECT 1 
                 FROM TABLE B 
                 WHERE B.STATE = 'INPROGRESS' 
                  AND A.LOCKID = B.LOCKID)

Upvotes: 0

Related Questions