CODEWITHSUNDEEP
mysqlsql
johowie
johowie

Reputation: 2495

combine 3 or more tables, deliberately without a joining condition

How do i join 3 or more tables in mysql as follows?

example with just two tables :

TableA:                  TableB          
ID   |   fieldA          ID   |   fieldB 
-----------------       -----------------
  1  |   valueA1           1  |   valueB1
  2  |   valueA2           2  |   valueB2


ResultTable:
newID  | ID  |  table  | fieldA  |  fieldB
---------------------------------------------
   1   |  1  |  TableA | valueA1 |  
   2   |  2  |  TableA | valueA2 |  
   3   |  1  |  TableB |         |  valueB1
   4   |  2  |  TableB |         |  valueB2

I know this probably sounds a bit weird!. I am going to try and use this to batch insert nodes for records from various tables into neojs graph database with this batch-insert script. which could be hilarious considering I hardly know what I am doing in either database ;-) .

Upvotes: 2

Views: 136

Answers (3)

John Woo
John Woo

Reputation: 263893

Try this one,

SELECT  @rownum := @rownum + 1 AS NewID,
        a.*
FROM
    (
        SELECT  ID, fieldA, '' AS fieldB
        FROM    tableA
        UNION ALL
        SELECT  ID, '' AS fieldA, fieldB
        FROM    tableB
    ) a, (SELECT @rownum:=0) r

SQLFiddle Demo

  • Create New Table

here's the proposed schema

CREATE TABLE Newtable
(
    NewID INT AUTO_INCREMENT,
    ID INT NOT NULL,
    FieldA VARCHAR(30),
    FieldB Varchar(30),
    CONSTRAINT tb_pk PRIMARY KEY (NewID)
)
  • then Insert your values,

here's the query using INSERT INTO...SELECT statement

INSERT INTO NewTable (ID, fieldA, fieldB)
SELECT  ID, fieldA, NULL AS fieldB
FROM    tableA
UNION ALL
SELECT  ID, NULL AS fieldA, fieldB
FROM    tableB

Upvotes: 3

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

  1. Create a table with auto increment newID
  2. Add all the possible columns allowing nulls.
  3. INSERT INTO it the values from TableA, then TableB with something like:

 

INSERT INTO table
(ID, `table`, fieldA)
SELECT ID, 'TableA', fieldA FROM TableA

INSERT INTO table
(ID, `table`, fieldB)
SELECT ID, 'TableB', fieldB FROM TableB

Upvotes: 1

Prasanna
Prasanna

Reputation: 4703

Use UNION to select all rows in one result set, INSERT INTO for inserting to new table. Also you can get new ID using ROW_NUMBER() in sql server

SELECT ID, COL1, NULL, NULL FROM Table1
UNION
SELECT ID, NULL, COL2, NULL FROM Table2
UNION
SELECT ID, NULL, NULL, COL3 FROM Table3

Select above result to a temp table. Use row number to update new ID

SELECT ID, ... , ROW_NUMBER() OVER(ORDER BY ID) AS NewID FROM #TempTable

Upvotes: 0

Related Questions