Noobster Noobster - 4 months ago 13
SQL Question

mysql - do not display data if a certain condition is met

Sorry I don't really know how to make a title for this because I can't explain it really. for example i have here a table

c_id emp_id clinic_id
1 1 1
2 1 2
3 2 1
4 3 3
5 1 3


now i will do a query like this

select distinct * from table where clinic_id <> 1


And the result would be

c_id emp_id clinic_id
2 1 2
4 3 3
5 1 3


at this point I need help, if from the
where clinic_id <> 1
a certain
emp_id
is within its row of condition(Sorry for my bad english). for example
emp_id 1
. All
emp_id 1
must not be display also.
So the result would be just

c_id emp_id clinic_id
4 3 3 // *The result I want*

Answer
SELECT DISTINCT * 
FROM table
WHERE clinic_id <> 1 
AND emp_id NOT IN 
    (SELECT DISTINCT emp_id 
            FROM table
            WHERE clinic_id = 1)

Try this one.

It uses the subquery to return the emp_ids which are in the same row as the 1 in the column clinic_id, and removes them from the resultset because you also don't want those emo_ids.

Comments