How do i compare 2 rows from the same table (SQL Server)

Question

I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id.

I know the syntax to get the row data but i have never tried comparing 2 rows from the same table before? How is it done? Would i need to use variables to store the data from each? Or some other way?

(Using SQL Server 2008)

Answer 1

 SELECT t1.value, t2.value
 FROM MyTable t1, MyTable t2
 WHERE t1.id = @id
   AND t1.status = @status1
   AND t2.id = t1.id
   AND t2.status = @status2

Answer 2

Hi,

Some people find the following alternative syntax easier to see what is going on.

select t1.value,t2.value
from MyTable t1
    inner join MyTable t2 on
     t1.id = t2.id
where t1.id = @id

Hope this helps but please feel free to ask for additonal assitance.

Cheers, John

Answer 3

You can join a table to itself as many times as you require, it is called a self join.

An alias is assigned to each instance of the table (as in the example below) to differentiate one from another.

select a.SelfJoinTableID from dbo.SelfJoinTable a
inner join dbo.SelfJoinTable b on a.SelfJoinTableID = b.SelfJoinTableID
inner join dbo.SelfJoinTable c on a.SelfJoinTableID = c.SelfJoinTableID
where a.Status = 'Status to filter a' and
b.Status = 'Status to filter b' and
c.Status = 'Status to filter c'

Answer 4

select * from A as b inner join A as c on b.a = c.a where b.a = 'some column value'