24

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)

peterh
  • 11,875
  • 18
  • 85
  • 108

6 Answers6

39

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'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Andy Jones
  • 1,472
  • 9
  • 15
  • 8
    Good for you for mentioning that you must alias – HLGEM Feb 04 '09 at 14:35
  • 17
    not really as many times as you require.. there is a limit of referencing 256 tables in query :] – pkmiec Sep 28 '11 at 10:31
  • @pkmiec as intriguing as it sounds, [Microsoft docs about SQL Server limitations](https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server) differ from your argument - `Tables per SELECT statement - Limited only by available resources`. I'm still not sure if I'm ever going to use this information unless I design something really horribly but I'd want to know how you got that number, it definitely looks intriguing. – Nikhil Girraj Jul 19 '17 at 08:04
  • 1
    @Nikhil Girraj Well.. I can't cite the source after such a long time. I think I stumbled upon that in SQL Server 2005... Current docs says that there are no limits but go ahead and try running such a SQL :) https://pastebin.com/hfSGiNkQ – pkmiec Jul 20 '17 at 22:05
  • 1
    @pkmiec I'm sorry I didn't notice how old this post was :). I'll accept that this is an old limitation which does not exist on the newer versions. And, I ran the query you referenced and the output came out to be `There is insufficient system memory in resource pool 'default' to run this query.` after 3 min. – Nikhil Girraj Jul 21 '17 at 08:09
14

OK, after 2 years it's finally time to correct the syntax:

SELECT  t1.value, t2.value
FROM    MyTable t1
JOIN    MyTable t2
ON      t1.id = t2.id
WHERE   t1.id = @id
        AND t1.status = @status1
        AND t2.status = @status2
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 5
    You should NOT use that syntax. Use specific joins instead. The left and right joins forms of that syntax are deprecated. I would not be surprised to find this form deprecated in the next version. – HLGEM Feb 04 '09 at 14:34
  • @HLGEM - Can you provide links as to where this is mentioned as deprecated please? – Pauk Nov 09 '10 at 10:54
  • 3
    @Pauk ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10sq_GetStart/html/c10eeaa5-3d3c-49b4-a4bd-5dc4fb190142.htm And even if they weren;t deprecated the left and right versions should not be used as they give inconsistent and sometimes inaccurate results. They can sometimes be interpreted as a cross join instead of a left or right join. – HLGEM Nov 09 '10 at 14:32
  • 1
    This link might be of more use than the ms-help guff: http://msdn.microsoft.com/en-us/library/dd172122.aspx – Pauk Nov 10 '10 at 13:43
9

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
John Sansom
  • 41,005
  • 9
  • 72
  • 84
3

SELECT COUNT(*) FROM (SELECT * FROM tbl WHERE id=1 UNION SELECT * FROM tbl WHERE id=2) a

If you got two rows, they different, if one - the same.

Валентин Нэллин
  • 41
  • 2
1
SELECT * FROM A AS b INNER JOIN A AS c ON b.a = c.a
WHERE b.a = 'some column value'
Shef
  • 44,808
  • 15
  • 79
  • 90
narender
  • 19
  • 1
0

I had a situation where I needed to compare each row of a table with the next row to it, (next here is relative to my problem specification) in the example next row is specified using the order by clause inside the row_number() function.

so I wrote this:

DECLARE @T TABLE (col1 nvarchar(50));

insert into @T VALUES ('A'),('B'),('C'),('D'),('E')

select I1.col1 Instance_One_Col, I2.col1 Instance_Two_Col  from (
 select col1,row_number() over (order by col1) as row_num
 FROM @T
) AS I1
left join (
 select col1,row_number() over (order by col1) as row_num
 FROM @T
) AS I2 on I1.row_num = I2.row_num - 1

after that I can compare each row to the next one as I need

Mohamed
  • 342
  • 1
  • 2
  • 11