CODEWITHSUNDEEP
mysqlsql
user1371033
user1371033

Reputation: 353

SQL:selecting rows matching certain criteria in a specific field

I have a situation where i need to select row from DB table which depends on the values in of the columns.

Project    Version     Status

MSS           3         active
MSS           2         active 
MSS           1         complete 
RIL           3         active
RIL           2         active  
RIL           1         active
DT            2         complete
DT            1         complete

A project is considered complete only if all its versions are complete. That means when status column has 'complete' in all occurrence of same project in the project column of the table.

How can select only those projects which are complete?

Upvotes: 2

Views: 2126

Answers (2)

Z .
Z .

Reputation: 12837

select project
from MyTable
group by project
having count(*) = sum(case when status = 'complete' then 1 else 0 end)

Upvotes: 3

Melanie
Melanie

Reputation: 3111

SELECT * FROM MyTable
WHERE Project IN
(SELECT Project FROM MyTable WHERE Status = 'complete')

Upvotes: 0

Related Questions