MS Sql combined with zero values

Question

I have 3 tables that look like this: 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 a result, I want to get a joined table, where there will be zero values ​​if there is no such data in any table. I want something to look like this:

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

I can’t understand how I can do this. I tried with external connections, but the result was repeated. Maybe you can use something like a group 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 the result that I want, and how can I do this?

Answer 1

-, Table1 N , N - Table2 Table3. , .

ROW_NUMBER, Table2 Table3 JOIN:

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

Answer 2

, FULL JOIN Table2 Table3 , Table1 FULL JOIN:

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