SELECTING with multiple WHERE conditions on same c

Question

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

My table is a very simple linking setup for applying flags to a user ...

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

etc... in this case you'll see both contact 99 and 100 are flagged as both "Volunteer" and "Uploaded"...

What I need to be able to do is return those contactid's ONLY that match multiple criteria entered via a search form...the contactid's have to match ALL chosen flags... in my head the SQL should look something like:

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

but... that returns nothing... What am I doing wrong here?

Answer 1

You can either 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 is unique).

Or use joins:

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 list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

Answer 2

AND will return you an answer only when both volunteer and uploaded are present in your column. Otherwise it will return null value...

try using OR in your statement ...

SELECT contactid  WHERE flag = 'Volunteer' OR flag = 'Uploaded'

Answer 3

Sometimes you can't see the wood for the trees :)

Your original SQL ..

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

Should be:

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

Answer 4

your code :

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

will not work, for you did not declare the table name. the execution will return an error message.

and if you want both search query to display, your code should look something like this.

SELECT * FROM (your_table_name) WHERE flag = 'Volunteer' OR flag = 'Uploaded';

and not like this because it will always return false SELECT * FROM (your_table_name) WHERE flag = 'Volunteer' AND flag = 'Uploaded';

you can also do this

SELECT * FROM (your_table_name) 
WHERE flag = 'Volunteer' OR flag = 'Uploaded' 
ORDER BY contactid, flag asc; 

(asc for ascending order, you can also change it to desc if you want it to display in descending order)

Answer 5

Change AND to OR. Simple mistake. Think of it like plain English, I want to select anything with that equals this or that.

Answer 6

Consider using INTERSECT like this:

SELECT contactid WHERE flag = 'Volunteer' 
INTERSECT
SELECT contactid WHERE flag = 'Uploaded'

I think it it the most logistic solution.