SELECTING with multiple WHERE conditions on the same column

Question

Okay, I think I might be overlooking something obvious/simple here... but I need to write a query that only returns records that match multiple criteria on the same column...

My table is a very simple link setup for applying flags to users...

ID contactid flag flag_type
----------------------------------
118 99 Volunteer 1
119 99 Uploaded 2
120 100 Via Import 3
121 100 Volunteer 1
122 100 Uploaded 2

Wait... In this case, you'll see that contacts 99 and 100 are both marked as "Volunteer" and "Uploaded"...

All I need to do is return those contactids that match multiple criteria entered via the search form... the contactid must match all selected flags... In my head, the SQL should look like: < ; /p>

SELECT contactid
 WHERE flag = 'Volunteer'
   AND flag = 'Uploaded'...

But... nothing is returned... What am I doing wrong here?

Answer 1

use:

SELECT t.contactid
    FROM YOUR_TABLE t
   WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY t.contactid
  HAVING COUNT(DISTINCT t.flag) = 2

The key is that the count of t.flag needs to be equal to the number of parameters in the IN clause.

The use of COUNT(DISTINCT t.flag) is to prevent the combination of contactid and flag from not having a unique constraint - if there is no chance of duplication, you can omit the DISTINCT from the query:

SELECT t.contactid
    FROM YOUR_TABLE t
   WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY t.contactid
  HAVING COUNT(t.flag) = 2

Answer 2

You can use GROUP BY and HAVING COUNT(*) = _:

SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list

(assuming contact_id, flag are unique).

Or use connection:

SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'

If the flag list is long and there are many matches, the first one may be faster. If the flag list is short and there are few matches, you may find the second one to be faster. If performance is an issue, try testing it on your data to see which one works best.