mysql: join two tables with two fields in common to get a third table

Question

I need some help using mysql and phpmyadmin; I have two tables with two column fields in common, and I need to join both tables into a third where this two fields of each table are in common; also the reference table is the table 1, so the idea is that 3rd table is actually table 1 with the aditional of table 2 adding value 1 and value 2 from table table 2 into table 1 where Column 1 and Column 2 are the same for both tables... for example:

    table1:
    ID, Column1, Column2, Value1, Value2
    1 ,   DAN     ,  Citi1 ,    1  ,   3
    2 ,   JUAN    ,  Citi1 ,    5  ,   5
    3 ,   DAN     ,  Citi2 ,    3  ,   7
    4 ,   PEDRO ,  Citi1 ,    2  ,   4
    5 ,   JUAN    ,  Citi2 ,    7  ,   9

    table2:
    ID, Column1, Column2, Value1, Value2
    1 ,   DAN   ,  Citi1 ,    5  ,   0
    2 ,   JUAN  ,  Citi1 ,    0  ,   3
    3 ,   DAN    ,  Citi2 ,    4  ,   5
    4 ,   JUAN  ,  Citi2 ,    6  ,   8



    table 3, join:

    ID, t1/t2.Column1 , t1/t2.Column2, t1.Value1, t1.Value2, t2.Value1, t2.Value2

    ID, t1.Column1, t1.Column2, t1.Value1, t1.Value2, t2.Value1, t2.Value2
    1 ,   DAN        ,  Citi1          ,    1         ,   3          ,       5       ,       0
    2 ,   JUAN       ,  Citi1          ,    5         ,   5          ,       0       ,       3
    3 ,   DAN         ,  Citi2          ,    3         ,   7          ,       4       ,       5
    4 ,   PEDRO     ,  Citi1          ,    2         ,   4          ,       0       ,       0
    5 ,   JUAN        ,  Citi2           ,    1        ,   3          ,       6       ,       8

This is horrendous. Please take a look at how others have asked questions. — Strawberry, Oct 29 '14 at 11:56
Basically asking "What is the Syntax for an SQL join statement", which the OP should type into Google verbatim and be done with it. — Felix Frank, Oct 29 '14 at 14:22

score 0 · Accepted Answer · edited May 23 '17 at 12:17

0

I suggest you may want to read some info about SQL JOIN. Here are some great examples.

This is the format you are probably looking for:

SELECT TableA.*, TableB.*, TableC.*, TableD.*
FROM TableA
    JOIN TableB
        ON TableB.aID = TableA.aID
    JOIN TableC
        ON TableC.cID = TableB.cID
    JOIN TableD
        ON TableD.dID = TableA.dID

Also, check out this post for examples and info.

For an insert you can do something like this for example:

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';

edited May 23 '17 at 12:17

Community

1
1

answered Oct 29 '14 at 11:57

Matheno

4,112
6
36
53

You can also put another query into your INSERT. Check the update – Matheno Oct 30 '14 at 13:05

mysql: join two tables with two fields in common to get a third table

1 Answers1