CODEWITHSUNDEEP
mysqlsql
Berk Özbalcı
Berk Özbalcı

Reputation: 3196

Concatenate two tables in MySQL

What I have:

Table1

╔════╦═══════╦═════╗
║ ID ║ NAME  ║ AGE ║
╠════╬═══════╬═════╣
║  0 ║ John  ║  15 ║
║  1 ║ Chris ║  20 ║
╚════╩═══════╩═════╝

Table2

╔════╦════════════╦═════════════╗
║ ID ║ OCCUPATION ║   ADDRESS   ║
╠════╬════════════╬═════════════╣
║  0 ║ Bus Driver ║ None        ║
║  1 ║ Lawyer     ║ some adress ║
╚════╩════════════╩═════════════╝

Desired output:

Table 3

╔════╦═══════╦═════╦════════════╦═════════════╗
║ ID ║ NAME  ║ AGE ║ OCCUPATION ║   ADDRESS   ║
╠════╬═══════╬═════╬════════════╬═════════════╣
║  0 ║ John  ║  15 ║ Bus Driver ║ None        ║
║  1 ║ Chris ║  20 ║ Lawyer     ║ some adress ║
╚════╩═══════╩═════╩════════════╩═════════════╝

While I can accomplish this task by using PHP mysql_query("SELECT .. "); mysql_query("INSERT .."); -ish ways, I want to use pure SQL for this task.

Upvotes: 2

Views: 2471

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

 SELECT t1.* , t2.* 
 FROM table1 t1, table2 t2
 WHERE t1.id = t2.id

SQL FIDDLE DEMO

Upvotes: 3

Orangecrush
Orangecrush

Reputation: 1990

This should do,

SELECT a.id, name, age, occupation, address
FROM table1 a, table2 b
WHERE a.id = b.id;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

JOIN the two tables:

SELECT
  t1.ID,
  t1.Name,
  t1.Age,
  t2.Occupation,
  t2.Address
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID;

SQL Fiddle Demo

This will give you:

| ID |  NAME | AGE | OCCUPATION |     ADDRESS |
-----------------------------------------------
|  0 |  John |  15 | Bus Driver |        None |
|  1 | Chris |  20 |     Lawyer | some adress |

If you want to create a new table table3 directly from this select, do this:

CREATE Table Table3
AS 
SELECT
  t1.Name,
  t1.Age,
  t2.Occupation,
  t2.Address
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID;

Like this

Upvotes: 5

Related Questions