CODEWITHSUNDEEP
mysqlsqlselect
Michal Vašíček
Michal Vašíček

Reputation: 78

SQL Selecting more rows that share one column and contain one of few values in another

let's say I have table like this:

+-----------------+-----------------+
| name            | playlist        |
+-----------------+-----------------+
| Jay-Z           | chill vibes     |
+-----------------+-----------------+
| Jay-Z           | rap             |
+-----------------+-----------------+
| Jay-Z           | discover weekly |
+-----------------+-----------------+
| Imagine Dragons | discover weekly |
+-----------------+-----------------+
| ZEDD            | discover weekly |
+-----------------+-----------------+
| Imagine Dragons | pop             |
+-----------------+-----------------+

I need to select an author that is in playlists DISCOVER WEEKLY and RAP. All I know are the playlists. How to do that with pure SQL, is that even possible?

Upvotes: 0

Views: 23

Answers (2)

jose_bacoy
jose_bacoy

Reputation: 12684

Use group by and having count. see below:

select name
from table1
where playlist in ('rap','discover weekly')
group by name
having count(distinct playlist) = 2;

Result:  Jay-Z

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

you could use a filter for having the number of distinct playlist equal at the number you are looking 

  select name  
  from my_table  
  where playlist in ('discover weekly', 'rap')
  group by name
  having count(distinct playlist) = 2

Upvotes: 2

Related Questions