CODEWITHSUNDEEP
phpmysqlselectjoin
jefffan24
jefffan24

Reputation: 1326

combining 2 tables in MySQL select statement

I know I can do joins but its not exactly what I want.

I'm making a live chat system that has 2 tables mainly: the main chat table (call it table a), and then a mod table (call this one table b). If a user gets suspended, messages reach over 100 for that channel, or they are over 1 week, the messages get moved from the main chat table to the mod table.

I store the ID of the chat messages as ID(primary) on the main chat table and as chatID on the mod table.

What I'm doing is making a separate page for my Mods and I want to be able to combine the two tables into 1 area but I want them to be ordered by their respective tables.

So lets say we had the following:

Main table ID's: 1,2,4 Mod table ID: 3

I want my results to show up 1,2,3,4 no matter which table the ID is in.

Any help is greatly appreciated!

Edit: I got the answer and this is what I used to do so:

SELECT ab.* FROM 
  ((SELECT ID as table_id FROM a 
    WHERE roomID = 'newUsers' ORDER BY ID ASC) 
UNION ALL 
  (SELECT chatID as table_id FROM b 
    WHERE roomID = 'newUsers' ORDER BY chatID ASC)) ab 
ORDER BY ab.table_id

Upvotes: 2

Views: 6231

Answers (1)

Johan
Johan

Reputation: 76537

Use a UNION in a subselect.

SELECT ab.* FROM (
  SELECT 1 as table_id, * FROM a
UNION ALL
  SELECT 2 as table_id, * FROM b
) ab
ORDER BY ab.id

If you want the result of table A to appear before table B, change the query to:

SELECT ab.* FROM (
  SELECT 1 as table_id, * FROM a
UNION ALL
  SELECT 2 as table_id, * FROM b
) ab
ORDER BY ab.table_id, ab.id

Some background
UNION ALL will merge two tables resultsets into one resultset.
UNION will do the same but will eliminate duplicate rows.
This takes time and slows things down, so if you know there will be no duplicate records (or you don't care about dups) use UNION ALL.

See also: http://dev.mysql.com/doc/refman/5.5/en/union.html

Upvotes: 6

Related Questions