MySQL selct entry with multiple values from same table

user_2985565 0
Viewed 30

I have two tables (as seen bellow) and i want to join them and select the person who has multiple values form the second table. The first table is a list of all people and their unique IDs, the second table is a list of peoples favorite colors.

Table A.: Unique ID | Name

+-----+-------------+
| uid | name        |
+-----+-------------+
| 321 | Ana         |
| 662 | Nick        |
| 003 | Fred        |
+-----+-------------+

Table B.: Table ID | Unique ID | Color ID

+----------+--------+-----------+
|       id | uid    | color_id  |
+----------+--------+-----------+
|        1 | 121    | 1         |
|        2 | 127    | 2         |
|        3 | 003    | 11        |
|        4 | 002    | 11        |
|        5 | 111    | 3         |
|        6 | 044    | 5         |
|        7 | 003    | 5         |
|        8 | 003    | 8         |
+----------+--------+-----------+

So i want to select only the users (uid) who matches all the colors given, for example 11 and 8 (red and fusia). In this case that would be user 003 | Fred, and only have 1 row per match, and not multiple (for each value).

I have tried using where color_id IN (x,y,z...) but this will return any person who has at least 1 color in the list

mysql
2 Answers

You can try below -

select uniqueid, name from tableA a
inner join tableB b on a.uniqueid=b.uid
where color_id in (11,8)
group by uniqueid, name
having count(distinct color_id)=2
user_69148640

you can do your wish by

    select distinct tableA.uid, tableA.name tableB.color_id from tableA 
    inner join tableB on tableA.uniqueid=tableB.uid
    group by tableA.uid , tableA.name ,tableB.color_id
    having count(distinct color_id)=2
user_101708210
Related