Group select statements together by common column in sql-server

Question

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.

Can you provide sample data for your tables `A`, `B`, and `C` please? Is the relationship between `A` and `B` and `A` and `C` one-to-one, or do you have a one-to-many relationship? What would you expect to see if `C` had related rows, and `B` only 2? — Thom A, Jun 24 '19 at 13:24

score 1 · Answer 1 · answered Jun 24 '19 at 13:30

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

Unfortunatly not, because both table B and C have a number of other columns (I edited my question) — Eike S, Jun 24 '19 at 13:33

score 0 · Answer 2 · answered Jun 24 '19 at 13:35

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

score 0 · Answer 3 · answered Jun 24 '19 at 13:39

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.

Thom A · Answer 4 · 2019-06-24T14:00:02.213

0

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

edited Jun 24 '19 at 14:00

answered Jun 24 '19 at 13:42

Thom A

88,727
11
45
75

The results doesn't look like what the OP provide, I don't think it's a sample data. It looks like `UNION / UNION ALL` nothing more, still the Q is unclear and deserve a CV tbh. – Ilyes Jun 24 '19 at 13:51
`ORDER BY` should have been `V.T`, @Sami. Fixed. – Thom A Jun 24 '19 at 14:00

Serg · Answer 5 · 2019-06-24T14:04:35.757

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;

Group select statements together by common column in sql-server

5 Answers5