CODEWITHSUNDEEP
mysqlsql
daktau
daktau

Reputation: 643

Simple SQL Query Help Required

I have this table

uid  |  rid
-----------
1    |    4
1    |   13
2    |    4
3    |   13

I want my query to return the uid where it has rid = 13 AND does not have rid = 4 so the result would be 3

So far I have

SELECT distinct uid
FROM test
WHERE 
ur.rid <> 4 
AND ur.rid = 13

but of course this is returning 1 and 3

What am I missing from my query to get only 3?

Many thanks.

Upvotes: 2

Views: 34

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You can add NOT EXISTS:

SELECT DISTINCT uid
FROM test t1
WHERE t1.rid = 13
  AND NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.uid = t1.uid
                    AND t2.rid = 4);

LiveDemo

or using aggregation:

SELECT uid
FROM test
GROUP BY uid
HAVING COUNT(CASE WHEN rid = 13 THEN 1 END) > 0
   AND COUNT(CASE WHEN rid = 4 THEN 1 END) = 0;

LiveDemo2

EDIT (by gordon):

The above answer is fine. I'm just adding this as a simplification to the second query (it didn't seem appropriate to put in a separate answer and it is too long for a comment):

SELECT uid
FROM test
GROUP BY uid
HAVING SUM(rid = 13) > 0 AND
       SUM(rid = 4) = 0;

It takes advantage of a nice feature of MySQL.

SqlFiddleDemo

Upvotes: 4

Related Questions