CODEWITHSUNDEEP
mysqlsql
Jaison John
Jaison John

Reputation: 65

Return rows where column matches all values in a set

I have a Table structure in mysql:

ID  USER_ID TYPE
1   1       B
2   3       B
3   4       B
4   3       C
5   3       D
6   4       C
7   4       D
8   3       B

Fiddle Link: http://sqlfiddle.com/#!2/7df38f/1

I have a requirement like get all 'USER_ID' having 'Type' as B and C. ie I need a result as below:

USER_ID
3
4

Upvotes: 1

Views: 122

Answers (7)

user1625871
user1625871

Reputation: 

SELECT user_id, count(*) cnt
FROM T1
WHERE type IN ('B', 'C')
GROUP BY user_id
HAVING cnt >= 2;

user_id cnt
3       3
4       2

Upvotes: 0

kwelsan
kwelsan

Reputation: 1219

Try below SQL:

SELECT user_id, COUNT(*) cnt
FROM T1
WHERE type IN ('B', 'C')
GROUP BY user_id
HAVING cnt >= 2;

SQL Fiddle

Upvotes: 0

Barmar
Barmar

Reputation: 780889

SELECT user_id, COUNT(*) cnt
FROM tablename
WHERE type IN ('B', 'C')
GROUP BY user_id
HAVING cnt = 2

DEMO

This assumes that user_id + type combinations are unique. If not, you can make a subquery that gets distinct values:

SELECT user_id, COUNT(*) cnt
FROM (SELECT distinct user_id, type
      FROM tablename
      WHERE type IN ('B', 'C')) x
GROUP BY user_id
HAVING cnt = 2

DEMO

Upvotes: 1

rakeshjain
rakeshjain

Reputation: 1761

Try this query

SELECT group_concat(`type`) AS types,user_id 
FROM users 
WHERE `type` IN('B','C') 
group by user_id 
HAVING FIND_IN_SET('B',types)>0 && FIND_IN_SET('C',types)>0

SQL Fiddle http://sqlfiddle.com/#!2/8ef8e/2

Upvotes: 1

DSS
DSS

Reputation: 7259

Please modify this to suit your needs:

select * from (select USER_ID from T1 where USER_ID IN 
(select USER_ID from T1 where T1.TYPE = 'B')) t
INNER JOIN  
((select USER_ID from T1 where USER_ID IN 
(select USER_ID from T1 where T1.TYPE = 'C'))) t2
on t.USER_ID = t2.USER_ID group by t.user_id

SQL FIDDLE

Edit: a better approach

select * from (select USER_ID from T1 where T1.TYPE = 'B') t
INNER JOIN  
(select USER_ID from T1 where T1.TYPE = 'C') t2
on t.USER_ID = t2.USER_ID group by t.user_id

Sql fiddle

Upvotes: 0

Andolasoft Inc
Andolasoft Inc

Reputation: 1296

Try this

SELECT distinct firsttable.user_id
FROM t1 firsttable, t1 secondtable
WHERE firsttable.type ='B' and secondtable.type ='C'  and firsttable.user_id =secondtable.user_id

SQLFiddle Demo

Upvotes: 1

linpingta
linpingta

Reputation: 2620

select USER_ID from ( 
    ( select USER_ID from table where TYPE = 'B' ) as t1 join 
    ( select USER_ID from table where TYPE = 'C' ) as t2 on
    on t1.USER_ID = t2.USER_ID );

Upvotes: -1

Related Questions