Select common colums for certain rows

Question

I have a table like this:

table: roomMembers
roomid    member
0         User1
0         User2
0         User3
1         User1
1         User2
1         User4
etc...

I need a SQL query that will return the roomid where both UserX and UserY is a member, for example, if i was to wrap it in a function it would look like:

findCommonRooms("User1", "User2");

In this particular case it should return an array of 0 and 1.

Could you provide us a sample of the output result you are expecting? I am not able to understand what you are trying to accomplish. — FutbolFan, Apr 17 '15 at 19:25

score 1 · Answer 1 · answered Apr 17 '15 at 19:46

If my understanding is correct, you could easily find the members who have actually been in same rooms before.

CREATE TABLE roomMembers
(
roomid int,
member varchar(10)
);


INSERT INTO roomMembers VALUES (0,'Member1')
INSERT INTO roomMembers VALUES (0,'Member2')
INSERT INTO roomMembers VALUES (0,'Member3')
INSERT INTO roomMembers VALUES (1,'Member1')
INSERT INTO roomMembers VALUES (1,'Member2')
INSERT INTO roomMembers VALUES (1,'Member4')

This should give you all the rooms where multiple people have stayed.

WITH q1
AS (
    SELECT member
        ,count(*) total_count
    FROM [dbo].[roomMembers]
    GROUP BY member
    HAVING count(*) > 1
    )
SELECT r.roomid
    ,q1.member
FROM q1
JOIN dbo.roomMembers r ON r.member = q1.member
order by r.roomid;

Hope this helps!

score 1 · Accepted Answer · answered Apr 17 '15 at 20:44

1

This query will give you the roomid where both 'User1' and 'User2' is member.

SELECT roomid
FROM roomMembers
WHERE member in ('User1','User2')
GROUP BY roomid
HAVING COUNT(1) > 1

answered Apr 17 '15 at 20:44

Nikhil K Mamotty

124
4

Select common colums for certain rows

2 Answers2