SQL query for multiple multi value

Question

I'm having a bit of an issue trying to query a table. I'm trying to query all the usersid that has multiple colors. So I expect the results to be just 222 because it has red and blue values.

I have tried:

SELECT userid FROM test_table WHERE color = 'red' AND color = 'blue';  
SELECT userid FROM test_table WHERE color LIKE '%red%' AND color LIKE '%blue%';

I tried using or for both of these queries but they return everything.

| userid | color |
+--------+-------+
|  222   | red   |
|  222   | red   |
|  333   | red   |
|  333   | red   |
|  222   | blue  |
|  444   | blue  |

What RDBMS are you using? meta.stackoverflow.com/questions/388759/… and look into EXISTS — Brad
– Brad, Commented Feb 2, 2021 at 20:29

Gordon Linoff · Accepted Answer · 2021-02-02 20:32:08Z

1

I think you can use aggregation and having:

select userid
from t
where color in ('red', 'blue')
group by userid
having count(distinct color) = 2;

answered Feb 2, 2021 at 20:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shannon Regan · Accepted Answer · 2021-02-02 21:00:24Z

This might be a bit of overkill but this should work. The 1st Temp table was necessary to filter out duplicates where one userid had multiple of the same color. Then the second to place a row number on each userid per color entry. then just filter to only those with more than one entry. I'm assuming the end product is a unique list of userids.

DROP TABLE IF EXISTS #TempTableColor1;
Select Distinct UserID, Color
INTO #TempTableColor1
From t
where color in ('red', 'blue')

DROP TABLE IF EXISTS #TempTableColor;
Select UserID, Row_Number () Over ( Partition By USERID Order By Color) RN
INTO #TempTableColor
From #TempTableColor1
Where color in ('red', 'blue')

Select 
Distinct UserID
From #TempTableColor
Where RN > 1
Order By UserID

Collectives™ on Stack Overflow

SQL query for multiple multi value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related