[mysql] 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?

This question is related to mysql sql aggregate-functions where-clause

The answer is


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.


can't really see your table, but flag cannot be both 'Volunteer' and 'Uploaded'. If you have multiple values in a column, you can use

WHERE flag LIKE "%Volunteer%" AND flag LIKE "%UPLOADED%"

not really applicable seeing the formatted table.


Consider using INTERSECT like this:

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

I think it it the most logistic solution.


Try to use this alternate query:

SELECT A.CONTACTID 
FROM (SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'VOLUNTEER')A , 
(SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'UPLOADED') B WHERE A.CONTACTID = B.CONTACTID;

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'

select purpose.pname,company.cname
from purpose
Inner Join company
on purpose.id=company.id
where pname='Fever' and cname='ABC' in (
  select mname
  from medication
  where mname like 'A%'
  order by mname
); 

Use this: For example:

select * from ACCOUNTS_DETAILS
where ACCOUNT_ID=1001
union
select * from ACCOUNTS_DETAILS
where ACCOUNT_ID=1002

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 thing is that the counting of t.flag needs to equal the number of arguments in the IN clause.

The use of COUNT(DISTINCT t.flag) is in case there isn't a unique constraint on the combination of contactid and flag -- if there's no chance of duplicates 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

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'...

SELECT contactid, Count(*) 
FROM <YOUR_TABLE> WHERE flag in ('Volunteer','Uploaded')  
GROUP BY contactid 
HAVING count(*)>1;

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


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to aggregate-functions

Spark SQL: apply aggregate functions to a list of columns GROUP BY without aggregate function GROUP BY + CASE statement must appear in the GROUP BY clause or be used in an aggregate function Naming returned columns in Pandas aggregate function? Concatenate multiple result rows of one column into one, group by another column How to include "zero" / "0" results in COUNT aggregate? Apply multiple functions to multiple groupby columns Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause Optimal way to concatenate/aggregate strings

Examples related to where-clause

Laravel where on relationship object MySQL Select last 7 days SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel Conditional WHERE clause in SQL Server Conditional WHERE clause with CASE statement in Oracle How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? MySQL Multiple Where Clause How to use If Statement in Where Clause in SQL? MySQL direct INSERT INTO with WHERE clause MySql Inner Join with WHERE clause