86483

Join two tables and save into third-sql

I want to join two tables.

TableA

+-------------------+--------+
| wordA(primarykey) | countA |
+-------------------+--------+
| abc               |     25 |
| abcd              |     29 |
| abcde             |     45 |
+-------------------+--------+</pre>

<hr>

TableB

+-------------------+--------+
| wordB(primarykey) | countB |
+-------------------+--------+
| ab                |     10 |
| abc               |     40 |
| abcde             |     90 |
| abcdef            |     55 |
+-------------------+--------+</pre>

<hr>

Desired output:

TableC

+--------+--------+--------+
|  word  | countA | countB |
+--------+--------+--------+
| ab     |      0 |     10 |
| abc    |     25 |    40  |
| abcd   |     29 |      0 |
| abcde  |     45 |     90 |
| abcdef |      0 |     55 |
+--------+--------+--------+</pre>

<hr>

I want to insert values of the desired output in TableC. Please provide some code. I tried this but the problem which I am getting is that I am not able to merge wordA and wordB.

Answer1:

Try this

<strong>Edited for MYSQL ONLY</strong>

SQL Fiddle DEMO

Insert into TableC(word , countA , countB) Select IFNULL(TableA.wordA , TableB.wordB) as word , IFNULL(TableA.countA , 0) as countA , IFNULL(TableB.countB , 0) as countB from TableA LEFT join TableB on TableA.wordA = TableB.wordB Union Select IFNULL(TableA.wordA , TableB.wordB) as word , IFNULL(TableA.countA , 0) as countA , IFNULL(TableB.countB , 0) from TableA RIGHT join TableB on TableA.wordA = TableB.wordB;

Answer2:

INSERT INTO TableC SELECT t.word, SUM(COALESCE(a.countA, 0)) AS CountA, SUM(COALESCE(b.countB, 0)) AS countB FROM ( SELECT wordA AS word FROM tableA UNION SELECT wordB FROM tableB ) AS t LEFT JOIN tableA AS a on t.word = a.wordA LEFT JOIN tableB AS b on t.word = b.wordb GROUP BY t.word

SQL Fiddle Demo

This will give you:

| WORD | COUNTA | COUNTB | |--------|--------|--------| | ab | 0 | 10 | | abc | 25 | 40 | | abcd | 29 | 0 | | abcde | 45 | 90 | | abcdef | 0 | 55 |

Answer3:

Insert into TableC select wordA as word, countA, 0 as countB from TableA where wordA not in (select wordB from tableB) union select wordB as word, 0 as countA, countB from TableB where wordB not in (select wordA from tableA) union select wordA as word, countA, countB from TableA join TableB on wordA=wordB order by word asc

SQL fiddle here

Recommend

  • Update a column in a table with values from two other tables
  • MySQL LEFT JOIN — How to still return results when leftmost table is empty?
  • Iterating Through Table in High-Performance Code
  • SQL Trigger Inserting from Multiple tables
  • ORA-00933: SQL command not properly ended in insert command
  • D3 v4 force layout disable animation on drag
  • Can I use jquery to blank textarea fields or ajax like input boxes?
  • Removing event listeners on automatically created multiple elements
  • Jquery resizable reposition handle after scroll
  • IE11 textarea loses focus if another textarea is disabled
  • HighCharts - Show tooltip on column where value is 0 or null
  • Real Time CountDown Timer In Python
  • How to use Windows Media Foundation with UWP without a topology
  • jQuery: How to AJAXify WordPress Search?
  • Salesforce Different WSDL files and when to use
  • NUnit 3.0 TestCase const custom object arguments
  • ASP.NET MVC 2 Preview 2 - display directory list rather than home/index
  • Mysql - How to search for 26 records that each begins with the letter of the alphabet?
  • Silverlight DependencyProperty.SetCurrentValue Equivalent
  • Zurb Foundation _global.scss meta styles for js?
  • Declaring variable dynamically in VB.net
  • Transactional Create with Validation in ServiceStack Redis Client
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • PHP - How to update data to MySQL when click a radio button
  • Running a C# exe file
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • How to model a transition system with SPIN
  • Convert array of 8 bytes to signed long in C++
  • How to make Safari send if-modified-since header?
  • ORA-29908: missing primary invocation for ancillary operator
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Acquiring multiple attributes from .xml file in c#
  • Understanding cpu registers
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Converting MP3 duration time
  • java string with new operator and a literal