CODEWITHSUNDEEP
phpmysql
Sanooj T
Sanooj T

Reputation: 1327

Select Unique value related to another column

I have a table like this:

╔════╦════════════╦══════════╗
║ id ║ project_id ║ document ║
╠════╬════════════╬══════════╣
║  1 ║     1      ║    3     ║
║  2 ║     1      ║    5     ║  
║  3 ║     2      ║    3     ║
║  4 ║     2      ║    4     ║
║  5 ║     3      ║    5     ║
║  6 ║     4      ║    2     ║
╚════╩════════════╩══════════╝

I want all project_id which do not have document = 5

In other words, I want this result:

╔════════════╗
║ project_id ║
╠════════════╣
║     2      ║
║     4      ║
╚════════════╝

Upvotes: 1

Views: 49

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

Here is a version you can try which uses joins:

SELECT t1.project_id
FROM
(
    SELECT project_id, COUNT(*) AS projectCount
    FROM mytable
    GROUP BY project_id
) t1
INNER JOIN
(
    SELECT project_id, COUNT(*) AS projectCount
    FROM mytable
    WHERE document <> 5
    GROUP BY project_id
) t2
    ON t1.project_id = t2.project_id AND t1.projectCount = t2.projectCount

Follow the link below for a running demo:

SQLFiddle

Upvotes: 1

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

SELECT DISTINCT project_id FROM project 
WHERE project_id NOT IN(
    SELECT project_id FROM project where document = 5
 )

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

Try this:

SELECT project_id
FROM mytable
GROUP BY project_id
HAVING COUNT(CASE WHEN document = 5 THEN 1 END) = 0

Demo here

Upvotes: 2

Related Questions