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

sql

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.

Best Answer

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)

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – Fetch the row which has the Max value for a column

I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

In other words: fetch the row from t1 where no other row exists with the same UserId and a greater Date.

(I put the identifier "Date" in delimiters because it's an SQL reserved word.)

In case if t1."Date" = t2."Date", doubling appears. Usually tables has auto_inc(seq) key, e.g. id. To avoid doubling can be used follows:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

Re comment from @Farhan:

Here's a more detailed explanation:

An outer join attempts to join t1 with t2. By default, all results of t1 are returned, and if there is a match in t2, it is also returned. If there is no match in t2 for a given row of t1, then the query still returns the row of t1, and uses NULL as a placeholder for all of t2's columns. That's just how outer joins work in general.

The trick in this query is to design the join's matching condition such that t2 must match the same userid, and a greater date. The idea being if a row exists in t2 that has a greater date, then the row in t1 it's compared against can't be the greatest date for that userid. But if there is no match -- i.e. if no row exists in t2 with a greater date than the row in t1 -- we know that the row in t1 was the row with the greatest date for the given userid.

In those cases (when there's no match), the columns of t2 will be NULL -- even the columns specified in the join condition. So that's why we use WHERE t2.UserId IS NULL, because we're searching for the cases where no row was found with a greater date for the given userid.

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – Fetch the row which has the Max value for a column

Related Topic