CODEWITHSUNDEEP
sqlsql-serverdatabase
Dot Net developer
Dot Net developer

Reputation: 436

Get intersecting rows based on a column value

I have a table in SQL Server.This is how data is stored currently in table:

table data

I want to get all common menu_id when multiple role_id are provided. For eg: I want menu_id 1 when role_id provided are 1 and 3 as menu_id 1 is common for all role_id specified. I have no clue what query should I use. Can anyone help me?

Thanks in advance!

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You seem to want menu_ids that have a list of role_id values. This should do what you want:

select menu_id
from t
where role_id in (1, 3)
group by menu_id
having count(*) = 2;  -- number of values in IN list

Here is a db<>fiddle.

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

use group by with having

select menu_id from tablename
group by menu_id
having count(distinct role_id)>1

Upvotes: 1

Related Questions