CTE joining the same table twice

Question

I have a table @MitoT with following code:

DECLARE @ResT UNIQUEIDENTIFIER = NEWID(), @ResC UNIQUEIDENTIFIER = NEWID();
DECLARE
    @MitoT TABLE (
        Pid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
        MitoResultID UNIQUEIDENTIFIER NOT NULL,
        Position VARCHAR(10) NOT NULL,
        Base VARCHAR(10) NOT NULL);

and populate it wits some data:

INSERT INTO @MitoT VALUES (NEWID(), @ResT, '1', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '3', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '4', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '5', 'N');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '1', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '3', 'C');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '5', '-');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '6', 'G');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '6', 'T');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '9', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '11', 'C');

so for any Position there can exist a row with MitoResultID = @ResT or with MitoResultID = @ResC or with both. Now I try a CTE that returns me every Position, BaseT (which is Base WHERE MitoResultID = @ResT) and BaseC (which is Base WHERE MitoResultID = @ResC) for all cases:

WITH Compare (PositionT, PositionC, BaseT, BaseC) AS (
    SELECT t.Position, c.Position, t.Base, c.Base
        FROM @MitoT t
        FULL OUTER JOIN @MitoT c ON t.Position = c.Position
        WHERE (t.MitoResultID = @ResT AND c.MitoResultID = @ResC)
        OR (t.MitoResultID = @ResT AND c.MitoResultID IS NULL)
        OR (t.MitoResultID IS NULL AND c.MitoResultID = @ResC)
        )
SELECT * FROM Compare

But apparently I'm doing it wrong because it shows only those Position for which both BaseT and BaseC exist. Please help me understand what is wrong.

UPDATE: The result looks like:

PositionT	PositionC	BaseT	BaseC
5	5	N	-
6	6	G	T
3	3	A	C
1	1	A	A

So i'm missing the rows for Positions like 4, 9, or 11 for which BaseT or BaseC is NULL

user_16925961 · Answer

As suggested by Nikola Markovinović in his comment:

SELECT t.Position, c.Position, t.Base, c.Base FROM (select * from @MitoT t where MitoResultID = @ResT) t FULL OUTER JOIN (select * from @MitoT c where MitoResultID = @ResC) c ON t.Position = c.Position

This works perfectly and CTE now looks like:

WITH Compare (PositionT, PositionC, BaseT, BaseC) AS (
    SELECT t.Position, c.Position, t.Base, c.Base 
        FROM (select * from @MitoT t where MitoResultID = @ResT) t 
        FULL OUTER JOIN (select * from @MitoT c where MitoResultID = @ResC) c 
        ON t.Position = c.Position
    )
SELECT * FROM Compare

with resulting table:

PositionT	PositionC	BaseT	BaseC
1	1	A	A
3	3	A	C
5	5	N	-
NULL	11	NULL	C
6	6	G	T
9	NULL	A	NULL
4	NULL	A	NULL

CTE joining the same table twice

1 Answers