SELECTING with multiple WHERE conditions on same column
Solution 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.
Solution 2
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
Solution 3
Consider using INTERSECT like this:
SELECT contactid WHERE flag = 'Volunteer'
INTERSECT
SELECT contactid WHERE flag = 'Uploaded'
I think it it the most logistic solution.
Solution 4
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.
Solution 5
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;
Related videos on Youtube
RyanNehring
Political Activist, Professional Malcontent, Author & Musician. True Boxing Fanatic. Coldfusion/Javascript Developer.
Updated on October 08, 2021Comments
-
RyanNehring over 2 years
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?
-
Johar Zaman about 5 yearsThis statement is not working because flag cannot be equal to 'Volunteer' and 'Upload' at the same time. You can use OR operator instead of AND and it will work.
-
-
OMG Ponies over 13 yearsThe problem with JOINs is if there's more than one record of a contactid associated with the "Uploaded" flag, there'll be duplicates for the T1 references.
-
Kildareflare almost 11 yearsI don't believe this will return the results the OP desired. The contactIds need to match ALL flags, not one or more of them.
-
kero over 10 yearsThis will show all entries that are
Volunteer
ORUploaded
but he wants entries that are twice in and have each once -
MickyD almost 9 years
-
Bangash over 5 yearsSELECT contact_id FROM your_table WHERE ( flag IN ('Volunteer', 'Uploaded', ...) and one_type = sometype_one ) AND flag IN ('someOtherVolunteer', 'Uploaded', ...) and second_type = sometype_two GROUP BY contact_id HAVING COUNT(*) = 2 Sir i have same senario like above query, but it's returning null. zero rows.
-
Sri about 4 yearsIn case of self join, for N number of values, you'll need N self join. In that example, what if you have to get the results with all these three flags "Uploaded", "Volunteer" and "Via Import " in it? So, i think using
GROUP BY
withHAVING COUNT
makes more sense. -
linkonabe almost 4 yearsSince the questioner tagged mysql, I think its good to point out that INTERSECT doesnt work for MySql
-
JulieC over 3 yearsThe questioner may have tagged mysql, but thumbs up for this TSQL user this worked swimmingly for.