CODEWITHSUNDEEP
sqlsql-serverdatabasejoinunion
Eike S
Eike S

Reputation: 361

Group select statements together by common column in sql-server

I need to combine two tables together based on a common column. Normally I would just use an inner join on the specific column (lets call it parentID), but I need the results to be in seperate rows.

Table A:

ID, ...

Table B:

ID, ParentID, SomeColB, ...

Table C:

ID, ParentID, SomeColC, ...

ParentID points to the ID of table A. The result should look as follows:

ParentID  ID_A  ID_B  SomeColB  SomeColC
1         10    20    'VAL_B1'  NULL
1         10    20    NULL      'VAL_C1'
2         11    21    'VAL_B2'  NULL
2         11    21    NULL      'VAL_C2'
...

So I want to alternate between selecting values from Table B and C and leave the remaining columns on null. How would I do that?

I tried joining them together but this results in results being put into a single row.

EDIT: Both Table B and C have a 1-n relationship to table A (one entry in table a can be referenced from multiple entries in table B and C). Table B and C don't reference each other and are completely independent of eachother.

Upvotes: 1

Views: 728

Answers (5)

Serg
Serg

Reputation: 22811

My guess it's a UNION of JOINs

SELECT A.ID AS ParentID, B.ID AS ID_B, null as ID_C, B.SomeColB, null as SomeColC --, ..      
FROM A
JOIN B ON A.ID = B.ParentID
UNION
SELECT A.ID AS ParentID, null, c.ID as ID_C, null, C.SomeColC --, .. 
FROM A
JOIN C ON A.ID = C.ParentID
ORDER BY ParentID, ID_B, ID_C;

To repeat ids wrap it with one more SELECT:

SELECT ParentID
 , max(ID_B) OVER(PARTITION BY ParentID) AS ID_B
 , max(ID_C) OVER(PARTITION BY ParentID) AS ID_C 
 , SomeColB, SomeColC --, --
FROM (
    SELECT A.ID AS ParentID, B.ID AS ID_B, null as ID_C, B.SomeColB, null as SomeColC --, ..      
    FROM A
    JOIN B ON A.ID = B.ParentID
    UNION
    SELECT A.ID AS ParentID, null, c.ID as ID_C, null, C.SomeColC --, .. 
    FROM A
    JOIN C ON A.ID = C.ParentID) t
ORDER BY ParentID, ID_B, ID_C;

Upvotes: 0

Thom A
Thom A

Reputation: 95554

Bit of a stab in the dark, but it gets you the result set you want based on the sample data:

WITH A AS(
    SELECT ID
    FROM (VALUES(1),(2)) V(ID)),
B AS(
    SELECT V.ID,
           V.ParentID,
           V.ColB
    FROM (VALUES(1, 10,'Val_B1'),
                (2,11,'Val_B2'))V(ParentID,ID, ColB)),
C AS(
    SELECT V.ID,
           V.ParentID,
           V.ColC
    FROM (VALUES(1,20,'Val_C1'),
                (2,21,'Val_C2'))V(ParentID,ID, ColC))
SELECT A.ID AS ParentID,
       B.ID AS ID_A,
       C.ID AS ID_B,
       B.ColB,       
       C.ColC
FROM A
     CROSS APPLY (VALUES('B'),('C'))V(T)
     LEFT JOIN B ON A.ID = B.ParentID
                AND V.T = 'B'
     LEFT JOIN C ON A.ID = C.ParentID
                AND V.T = 'C'
ORDER BY A.ID,
         V.T;

DB<>fiddle

Upvotes: 0

gmiley
gmiley

Reputation: 6604

Looks like what you really want is a LEFT OUTER JOIN.

A slimmed down version of your select with the pertinent fields would look like this...

select a.ID as ParentID, b.SomeCol as SomeColB, c.SomeCol as SomeColC
from tableA a
left outer join tableB b
on b.ID = a.ID
left outer join tableC c
on c.ID = a.ID
;

Left outer joins include non-matching rows from the left table in the join, providing NULL values for the fields coming from the unmatched records in the table to the right in the join.

Upvotes: 0

M Bagheri
M Bagheri

Reputation: 21

You should use union operator

SELECT  IDA, ParentIDA, SomeColA FROM first_table  
UNION  
SELECT  IDB, ParentIDB, SomeColB FROM second_table

UNION will skip the duplicate recored if you want to show the duplicate records you should use UNION ALL operator

Upvotes: 0

DarkMark
DarkMark

Reputation: 129

Would something like this work for you? I've used a UNION to get both sets of data per ParentID:

   SELECT
        *
    FROM (
        SELECT
            ParentID,
            ID_A,
            ID_B,
            SomeCol B,
            NULL AS SomeColC
        FROM
            TableA
        UNION
        SELECT
            ParentID,
            ID_A,
            ID_B,
            NULL AS SomeColB,
            SomeColC
        FROM
            TableB
        )
    ORDER BY
        ParentID,
        SomeColB,
        SomeColC

Upvotes: 1

Related Questions