self join - resolve Tsql
-
Hi,
i am having the table as below
Col1Col2
G1G2
G3P1
P1P2
P2G4
G5G6
G7G8
but my expected out is :
G1G2
G3G4
G5G6
G7G8
can you guys provide solution for this.
-
I've had a brief look into my crystal ball, but for some reason it won't show me the query which you have used, nor the data in your source tables.
It is impossible to (helpfully) answer your question without more information. Please see the link in my signature for an explanation of how to submit questions such as this. In a nutshell, we need sample DDL and sample data, in a consumable format (so people can cut & paste into SSMS).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Looks like a straightforward recursive query
DECLARE @t TABLE(Col1 CHAR(2), Col2 CHAR(2))INSERT INTO @t(Col1,Col2)
VALUES
('G1', 'G2'),
('G3', 'P1'),
('P1', 'P2'),
('P2', 'G4'),
('G5', 'G6'),
('G7', 'G8');
WITH Recur AS (
SELECT t1.Col1 As Root,t1.Col2
FROM @t t1
WHERE NOT EXISTS(SELECT * FROM @t t2 WHERE t2.Col2=t1.Col1)
UNION ALL
SELECT r.Root,t.Col2
FROM Recur r
INNER JOIN @t t ON t.Col1=r.Col2)
SELECT Root AS Col1,Col2
FROM Recur r
WHERE NOT EXISTS(SELECT * FROM @t t WHERE r.Col2=t.Col1)
ORDER BY Col1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply