Question:

I'm not sure how to concisely formulate question to describe a problem I would like to solve.<br /> I have two following tables:<br />Table 1

[idA] [numA] NULL 8 1 10 2 15 3 16

Table 2

[idB] [numB] 2 14 3 30 4 32

Now, I'm not sure how to formulate T-Sql query to produce following result:

[id] [numA] [numB] NULL 8 0 1 10 0 2 15 14 3 16 30 4 0 32

Are there any suggestions on how to solve this?

<strong>UPDATE:</strong>

<hr />

Would there be any problems with @AdaTheDev's script if there was one more table (idC, numC) to join? In that case what would be the best solution? The thing is I have 15 of them to join into one table and they should be grouped by id and have 15 corresponding numX columns.

Something like this, should do it

SELECT ISNULL(t1.idA, t2.idB) AS id, ISNULL(t1.numA, 0) AS numA, ISNULL(t2.NumB, 0) AS numB FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.idA = t2.idB OR t1.ida IS NULL AND t2.idb IS NULL

<strong>Update</strong><br /> Note I've added an OR condition to the join to handle the case where idA and idB are NULL, to give a single result

Full test script (with added NULL id record in table2):

DECLARE @Table1 TABLE (ida integer, numA INTEGER) DECLARE @Table2 TABLE (idb integer, numb INTEGER) INSERT @Table1 ([ida], [numA]) VALUES (NULL, 8), (1, 10), (2, 15), (3, 16) INSERT @Table2 ([idb], [numb]) VALUES (NULL, 9), (2, 14), (3, 30), (4, 32) SELECT ISNULL(t1.idA, t2.idB) AS id, ISNULL(t1.numA, 0) AS numA, ISNULL(t2.NumB, 0) AS numB FROM @table1 t1 FULL OUTER JOIN @table2 t2 ON t1.idA = t2.idB OR t1.ida IS NULL AND t2.idb IS NULL

DECLARE @table1 AS TABLE (idA INT, numA INT) DECLARE @table2 AS TABLE (idB INT, numB INT) INSERT INTO @table1 VALUES (NULL, 8), (1, 10), (2, 15), (3, 16) INSERT INTO @table2 VALUES (2, 14), (3, 30), (4, 32) SELECT COALESCE(ida, idb) AS id, ISNULL(numa, 0) AS numa, ISNULL(numb, 0) AS numb FROM @table1 FULL OUTER JOIN @table2 ON ida = idb

Is this what you're after?

select tableA.idA as Id, tableA.numA as numA, tableB.numB as numB from tableA inner join tableB on tableA.Id = tableB.Id