CODEWITHSUNDEEP
sql
Shlomo
Shlomo

Reputation: 3990

Select all rows that equal on same column (unknown) value

My table lists threads between x users. Each user can be in y threads.

Below, user_id 1 is in thread 1 and 2, and so on...

Now, lets say user_id 1 is given only, how can I get all rows that have the same thread_id as the row that has user_id 1?

       id   user_id  thread_id
        1     1        1    
        2     1        2    
        3     2        1    
        4     3        2

Have no clue...

EDIT

Given are the user_uids 1 and 2. Now get me the thread_id that only both of them share. I dont want all threads of 1 and 2 but only the one that they share together.

Upvotes: 1

Views: 8721

Answers (5)

paulie4
paulie4

Reputation: 502

SELECT * FROM table
WHERE user_id!=1 AND
      thread_id IN (SELECT thread_id FROM table WHERE user_id=1);

New answer based on Indianer's edited description of question:

SELECT * FROM table t1
JOIN table t2 ON t1.thread_id=t2.thread_id AND t1.user_id!=t2.user_id
WHERE t1.user_id IN (1,2) AND t2.user_id IN (1,2);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If you want to find pairs of users that have exactly the same set of threads, then the following query will work:

select t.user_id, t2.user_id
from (select t.*, count(*) over (partition by t.user_id) as NumThreads
      from t
     ) t left outer join
     t t2
     on t.thread_id = t2.thread_id and t.user_id <> t2.user_id
group by t.user_id, t2.user_id
having SUM(case when t.user_id is null then 1 else 0 end) = 0 and
       SUM(case when t2.user_id is null then 1 else 0 end) = 0 and
       count(*) = max(t.NumThreads)

In this query, wherever it says "from t t2" you would put "from t2". Where the line is "from t" you would put "from t".

The structure of this query is a self-join with an aggregation. The intention to to find every pair of matching threads. Then to group them by the two user_ids. If the number of matching threads is the number of threads for the user (the third condition) and no threads fail to match (the first two conditions), then the two users match on all their threads.

Your question in the comments about threads on all users is easier.

select thread_id
from t cross join
     (select count(distinct user_id) as NumUsers from t) const
group by thread_id
having count(*) = max(const.NumUsers)

Upvotes: 2

dave823
dave823

Reputation: 1211

select distinct thread_id from tableX
where thread_id in (select thread_id from tableX where user_id=1)
and thread_id in (select thread_id from tableX where user_id=2)

I have modified my answer to fit your comment clarification. Give this a shot.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460138

You can use EXISTS:

DECLARE @userID INT 
SET @userID = 1 

SELECT u1.id, u1.user_id, u1.thread_id 
FROM   userthreads u1 
WHERE  u1.userid <> @userId 
       AND EXISTS(SELECT 1 
                  FROM   userthreads u2 
                  WHERE  u2.userid <> u1.userid 
                         AND u2.threadid = u1.threadid)

DEMO

If you want to include the user with the given userID omit the WHERE u2.userid <> u1.userid.

Upvotes: 1

Z .
Z .

Reputation: 12837

declare @id int
set @id = (select thread_id from threads where user_id = 1)

select * from threads where thread_id = @id

Upvotes: 1

Related Questions