CODEWITHSUNDEEP
mysqlsql
Root
Root

Reputation: 2349

Select data from two table as one table

I have two similar tables and want to give a result from two tables with one query with a result like one table ( rows as tandem and order with time )

MyTable1: 

time    name    Comment
-------------------------
45      asd     testasd
49      gas     testgas
50      has     testhas
96      bag     testbag

MyTable2: 

time    name    Comment
-------------------------
48      hjasf   bigasd
54      adg     biggas
65      zxx     bighas
115     cxx     bobbag
131     xxb     bobhas

RESULT: 

time    name    Comment
-------------------------
45      asd     testasd
48      hjasf   bigasd
49      gas     testgas
50      has     testhas
54      adg     biggas
65      zxx     bighas
96      bag     testbag
115     cxx     bobbag
131     xxb     bobhas

I try to do it but don't know , I must use JOIN or UNION or ... ?

Upvotes: 2

Views: 1858

Answers (4)

IanO.S.
IanO.S.

Reputation: 1382

http://www.w3schools.com/sql/sql_union.asp

Use select statements with union all in between to achieve this

Upvotes: 1

John Woo
John Woo

Reputation: 263723

You just need to use UNION ALL (ALL - to allow duplicates.)

SELECT time, name, Comment FROM table1
UNION ALL
SELECT time, name, Comment FROM table2
ORDER BY time

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

The output will look like this,

╔══════╦═══════╦═════════╗
║ TIME ║ NAME  ║ COMMENT ║
╠══════╬═══════╬═════════╣
║   45 ║ asd   ║ testasd ║
║   48 ║ hjasf ║ bigasd  ║
║   49 ║ gas   ║ testgas ║
║   50 ║ has   ║ testhas ║
║   54 ║ adg   ║ biggas  ║
║   65 ║ zxx   ║ bighas  ║
║   96 ║ bag   ║ testbag ║
║  115 ║ cxx   ║ bobbag  ║
║  131 ║ xxb   ║ bobhas  ║
╚══════╩═══════╩═════════╝

Upvotes: 3

neelsg
neelsg

Reputation: 4842

Use a UNION ALL as follows:

SELECT `time`, `name`, `Comment` FROM `MyTable1`
UNION ALL
SELECT `time`, `name`, `Comment` FROM `MyTable2`

To order by time try:

SELECT * FROM (
SELECT `time`, `name`, `Comment` FROM `MyTable1`
UNION ALL
SELECT `time`, `name`, `Comment` FROM `MyTable2`
) AS `Result` ORDER BY `time` ASC

Upvotes: 0

fthiella
fthiella

Reputation: 49049

If both tables have the same number of fields, you could use this:

SELECT * from MyTable1
UNION ALL
SELECT * from MyTable2
ORDER BY time

UNION ALL will select all rows of the first query, combined with all of the rows of the second query. If you need to remove duplicates you should use UNION instead of UNION ALL. The ORDER BY clause will order the resulting rows by time.

See documentation of UNION clause here.

Upvotes: 1

Related Questions