60582

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

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.

Answer1:

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

Answer2:

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

Answer3:

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

Recommend

  • 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