Joining tables on columns with different names but produce single column in result


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?


<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


  • Mongo query to sort by distinct count
  • “Your API key is not valid on this domain” when calling Disqus from WP7
  • c# linq to sql iterating through left join results
  • NHibernate Mapping multiple tables to one class
  • Django model inheritance, filtering models
  • How can I set a binding to a Combox in a UserControl?
  • Does it make sense to call System.gc() and Thread.sleep() when working on Bitmaps?
  • Alternative To body {overflow:scroll;} That Will Prevent Page Jostling/Wriggling?
  • How can I sort a a table with VBA with given text condition?
  • With Hadoop, can I create a tasktracker on a machine that isn't running a datanode?
  • How to use an array of arrays with array_map(…) in PHP?
  • Django: Count of Group Elements
  • All Classes Conforming to Protocol Inherit Default Implementation
  • DomPDF {PAGE_NUM} not on first page
  • Is there a javascript serializer for JSON.Net?
  • Javascript simulate pressing enter in input box
  • Read text file and split every line in MSBuild
  • Fetching methods from BroadcastReceiver to update UI
  • Problems to linebreak with an int in JLabel
  • Excel - Autoshape get it's name from cell (value)
  • Check if a string to interpolate provides expected placeholders
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • Importing jscolor library in angular 2
  • How to make Safari send if-modified-since header?
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • How to pass list parameters for each object using Spring MVC?
  • RestKit - RKRequestDelegate does not exist
  • Unanticipated behavior
  • Traverse Array and Display in markup
  • Comma separated Values
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • A cron job substitute?
  • Trying to get generic when generic is not available
  • Understanding cpu registers
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • Add sale price programmatically to product variations
  • JaxB to read class hierarchy