How to read records that matches multiple values in the same field?

Question

I am trying to read records that match multiple values in the same field. Example table : A

UserID  License
1   DOG
1   CAT
1   FISH
2   DOG
2   CAT
3   FISH
4   CAT
4   DOG
4   FISH
5   CAT
5   FISH

How can I pull the UserIDs that have all three Licenses DOG, CAT and FISH ?

I am not sure how I could use INNER JOIN here since there are no two tables. What is the best approach here ?

Thank you PG

A l w a y s S u n n y · Accepted Answer · 2018-10-17T02:17:24.900

3

Don't even think about unnecessary INNER JOIN for this simple task. Let's do it using IN

SELECT UserID FROM A WHERE License IN('DOG','CAT','FISH');

N.B But still I'm little bit confused with this line though How can I pull the UserIDs that have all three Licenses DOG, CAT and FISH ?

Edited: This eliminate my confusion about all, try like this way and grab only the UserId from the output.

SELECT UserID,count(*) as lnt FROM A WHERE 
  License IN('DOG','CAT','FISH') 
group by UserId having lnt = 3;

Output:

UserID  lnt
  1     3
  4     3

Fiddle: http://sqlfiddle.com/#!9/e563ef/4

edited Oct 17 '18 at 02:17

answered Oct 17 '18 at 01:30

A l w a y s S u n n y

36,497
8
60
103

Just want to elaborate `Join : combines columns from one or more tables in a relational database`. +1 for fast typing.. :D – dwir182 Oct 17 '18 at 01:38
This would retrieve a `UserID` that has ANY dog, cat, or fish license, not only users that have ALL three. http://sqlfiddle.com/#!9/e563ef/1 `IN` is basically a shortened syntax of `OR` – Will B. Oct 17 '18 at 01:51
@fyrye thanks mate, you've cleared my confustion about the *all* three. I've modified my answer to return only ids that has **all** three value like DOG, CAT, FISH, Thnkx. again. – A l w a y s S u n n y Oct 17 '18 at 02:06
Great . . thanks for the help. I never thought of 'IN' – PCG Oct 17 '18 at 04:55

score 3 · Answer 2 · edited Oct 17 '18 at 02:04

3

You can use case when... in your sql, like this:

SELECT UserID, SUM(CASE WHEN License IN ("DOG", "CAT", "FISH") THEN 1 ELSE 0 END) AS LICENSE_COUNT
FROM EXAMPLE_TABLE
GROUP BY UserID
HAVING LICENSE_COUNT = 3

edited Oct 17 '18 at 02:04

Will B.

17,883
4
67
69

answered Oct 17 '18 at 01:57

yusher

226
1
5

Please correct the syntax error. You might try creating a fiddle at e.g. sqlfiddle.com or dbfiddle.uk to verify that the code you post will run, and produce the results you expect. Thanks. – Bob Jarvis - Слава Україні Oct 17 '18 at 01:58
I have verified my sql on my computer and it worked!! – yusher Oct 17 '18 at 02:02
My mistake - I didn't realize MySQL accepted double-quotes as string delimiters. – Bob Jarvis - Слава Україні Oct 17 '18 at 02:22

score 1 · Answer 3 · answered Oct 17 '18 at 01:49

1

"How can I pull the UserIDs that have all three Licenses DOG, CAT and FISH ?"

Like this:

SELECT USERID, COUNT(*) AS LICENSE_COUNT
  FROM EXAMPLE_TABLE
  GROUP BY USERID
  HAVING COUNT(*) = 3

dbfiddle here

answered Oct 17 '18 at 01:49

Bob Jarvis - Слава Україні

48,992
9
77
110

1

This would provide false positives if a license other than dog, cat or fish were added or already in the database. – Will B. Oct 17 '18 at 02:00
That's a different question. :-) – Bob Jarvis - Слава Україні Oct 17 '18 at 02:00

score 1 · Answer 4 · answered Oct 17 '18 at 02:04

1

try to like this:

select userid 
from example_table 
WHERE License IN('DOG','CAT','FISH') group by user_id having count(distinct License)=3

answered Oct 17 '18 at 02:04

feng.chen

11
1

score 1 · Answer 5 · answered Oct 27 '18 at 14:30

As other answers stated, it is better to use IN operator instead of Join. You can get the desired result even without using group by or HAVING. Try this

Select DISTINCT(UserId) from A 
Where UserID IN(Select userId from A where License='DOG') 
AND UserID IN (Select UserId from A where License='CAT') 
AND UserID IN (Select UserId from A where License='FISH')

How to read records that matches multiple values in the same field?

5 Answers5