CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Gleb
Gleb

Reputation: 1432

MS Sql join with null values

I have 3 tables looks like: Table1:

╔════╦═══════╗
║ id ║ name  ║
╠════╬═══════╣
║  1 ║ name1 ║
╚════╩═══════╝

Table2:

╔════╦════════════╗
║ id ║   data1    ║
╠════╬════════════╣
║  1 ║ some data1 ║
║  1 ║ some data2 ║
║  1 ║ some data3 ║
╚════╩════════════╝

Table3:

╔════╦═══════╗
║ id ║ data2 ║
╠════╬═══════╣
║  1 ║   456 ║
║  1 ║   345 ║
╚════╩═══════╝

As result I want to get joined table where will be null values if there is no such data in some table. I want to get something looks like this:

╔════╦═══════╦════════════╦════════╗
║ id ║ name  ║   data1    ║  data2 ║
╠════╬═══════╬════════════╬════════╣
║  1 ║ name1 ║ some data1 ║ 456    ║
║  1 ║ name1 ║ some data2 ║ 345    ║
║  1 ║ name1 ║ some data3 ║ null   ║
╚════╩═══════╩════════════╩════════╝

I can't figure out how can I do this. I was trying with outer joins but result have repeats. Maybe it is possible to use something like group by or other agregate function?

Now my code is:

SELECT * FROM Table1 t1
left outer join Table2 t2 on t1.id=t2.id
left outer join Table3 t3 on t1.id=t3.id

Is it possible to get result I want and how I can do this?

Upvotes: 4

Views: 121

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

It seems like you want a FULL JOIN between Table2 and Table3 based on row number and then combine every row of Table1 with the product of this FULL JOIN operation:

SELECT t1.id, t1.name, t2.data1, t3.data2
FROM Table1 AS t1
CROSS JOIN (
  SELECT data1, 
         ROW_NUMBER() OVER (ORDER BY data1) AS rn
  FROM Table2) AS t2
FULL OUTER JOIN (
  SELECT data2, 
         ROW_NUMBER() OVER (ORDER BY data2) AS rn
  FROM Table3) AS t3
ON t2.rn = t3.rn

Demo here

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

First, you need to duplicate each row from Table1 up to N times, where N is the max count of related rows from Table2 and Table3. This can be done using a tally table.

And then, use ROW_NUMBER to add another id to Table2 and Table3 and use that newly created id in the JOIN condition:

SQL Fiddle

DECLARE @maxCount INT

SELECT @maxCount =  MAX(cnt)
FROM (
    SELECT COUNT(*) AS cnt FROM Table1 GROUP BY id UNION ALL
    SELECT COUNT(*) AS cnt FROM Table2 GROUP BY id UNION ALL
    SELECT COUNT(*) AS cnt FROM Table3 GROUP BY id 
) t 

;WITH Tally AS(
    SELECT TOP (@maxCount) 
        N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
),
Cte AS(
    SELECT t1.*, cnt = x.cnt
    FROM Table1 t1
    OUTER APPLY(
        SELECT TOP 1 cnt
        FROM (
            SELECT COUNT(*) AS cnt FROM Table1 WHERE id = t1.id UNION ALL
            SELECT COUNT(*) AS cnt FROM Table2 WHERE id = t1.id UNION ALL
            SELECT COUNT(*) AS cnt FROM Table3 WHERE id = t1.id 
        ) t
        ORDER BY cnt DESC
    )x
),
CteTable1 AS(
    SELECT t1.*, rn = t.N
    FROM Cte t1
    CROSS JOIN Tally t
    WHERE t.N <= t1.cnt
),
CteTable2 AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY data1)
    FROM Table2
),
CteTable3 AS( 
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY data2)
    FROM Table3
)
SELECT
    t1.id, t1.name, t2.data1, t3.data2
FROM CteTable1 t1
LEFT JOIN CteTable2 t2
    ON t2.id = t1.id
    AND t2.rn = t1.rn
LEFT JOIN CteTable3 t3
    ON t3.id = t1.id
    AND t3.rn = t1.rn

Upvotes: 3

Related Questions