Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 self-join question

Author  Topic 

irvin
Starting Member

3 Posts

Posted - 2003-04-15 : 15:35:40
Hey to all! Need your help.
I have a table:

ID Col1 Col2
---- ------ ------
A 1 2
B 1 2
A 1 3
C 2 1
B 1 3
A 2 4

I have to compare records with Id=A and ID=B and exclude from select statement
equal records for ID=A and ID=B.
So I need to get:


A 2 4

I used self-join, but not shure, if I got the right result.
Any comments?
Irvin


X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 15:50:51
How about:


CREATE TABLE Table1 (IDCol char(1) ,Col1 Int ,Col2 Int)
GO

INSERT INTO Table1 (IDCol, Col1, Col2)
SELECT 'A', 1, 2 UNION ALL
SELECT 'B', 1, 2 UNION ALL
SELECT 'A', 1, 3 UNION ALL
SELECT 'C', 2, 1 UNION ALL
SELECT 'B', 1, 3 UNION ALL
SELECT 'A', 2, 4
GO

SELECT IDCol, Col1, Col2 FROM Table1 o
WHERE IDCol In ('A','B')
AND NOT EXISTS (SELECT 1 FROM Table1 a INNER JOIN Table1 b
ON a.Col1 = b.Col1 AND a.Col2 = b.Col2
WHERE a.IDCol = 'A' And b.IDCol = 'B'
AND ( o.IDCol = a.IDCol AND o.Col1 = a.Col1 AND o.Col2 = a.Col2
OR o.IDCol = b.IDCol AND o.Col1 = b.Col1 AND o.Col2 = b.Col2)
)
GO

DROP TABLE Table1
GO



Brett

8-)
Go to Top of Page

irvin
Starting Member

3 Posts

Posted - 2003-04-15 : 16:06:14
Thanks a lot! It works fine.
Irvin

Go to Top of Page

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-04-15 : 16:34:29
Thanks for the challenge. I needed a break from monotony.

Here is my answer:


SELECT * FROM T T2
WHERE Col2 NOT IN
(SELECT Col2 FROM T T1
WHERE T2.Col1 = t1.Col1 AND ID = 'B') AND ID = 'A'


--SMerrill
Seattle, WA
Go to Top of Page

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-04-15 : 16:47:52
... or perhaps more robust yet:

If you had a "B 1 1" record, and you needed to find it:


SELECT * FROM T
WHERE ID IN ('A', 'B') AND Col2 NOT IN
(SELECT Col2 FROM T T1
WHERE T.Col1 = t1.Col1 AND T.ID <> T1.ID)


--SMerrill
Seattle, WA
Go to Top of Page

irvin
Starting Member

3 Posts

Posted - 2003-04-15 : 16:59:35
SMerrill,
I don't think the second script is a good solution, because actually I have many ID in table and WHERE clause will not work properly. But thanks anyway, I got the idea.
Irvin

Go to Top of Page
   

- Advertisement -