MS Sql join with null values

Question

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?

"Some data2" in table two does not map to 345 in table three. So, some data 1 should repeat twice. And your query seems fine. — Abdullah Nehir
– Abdullah Nehir, Commented Dec 9, 2015 at 6:42
If you want "some data2" to map to 345, change this line in table3 "1, 345" to "2, 345" — Abdullah Nehir
– Abdullah Nehir, Commented Dec 9, 2015 at 6:44

Felix Pamittan · Accepted Answer · 2015-12-09 07:25:30Z

3

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

edited Dec 9, 2015 at 7:25

answered Dec 9, 2015 at 6:42

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shree.pat18 Over a year ago

Consider adding the caveat that this works for SQL Server 2005 onwards, but can be simulated for others

zedfoxus Over a year ago

Pretty slick solution!

Gleb Over a year ago

That's works well, thank you. But what if i don't what table have more rows then another? For example: I have 3 rows in Table3 and 2 rows in Table2.

Giorgos Betsos · Accepted Answer · 2015-12-09 08:45:15Z

0

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

answered Dec 9, 2015 at 8:45

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Collectives™ on Stack Overflow

MS Sql join with null values

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related