Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECTING with multiple WHERE conditions on same column

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?

like image 683
RyanNehring Avatar asked Oct 28 '10 21:10

RyanNehring


People also ask

How do I SELECT multiple values from the same column in SQL?

Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);

How do you use multiple WHERE conditions?

You can use the OR condition in the WHERE clause to test multiple conditions where the record is returned if any one of the conditions are met. This example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition.

How do you SELECT multiple conditions in a query?

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.


1 Answers

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.

like image 196
Mark Byers Avatar answered Oct 06 '22 00:10

Mark Byers