[MYSQL] Combining rows from different tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

[MYSQL] Combining rows from different tables

Post by lenton »

I have two tables:

Code: Select all

-----------------
|  name  |  num |
-----------------
|  herp  |  30  |
|  derp  |  10  |
|  werp  |  50  |
-----------------

Code: Select all

-----------------
|  name  |  num |
-----------------
|  ferp  |  40  |
|  cerp  |  20  |
|  yerp  |  60  |
-----------------
How can I combine the rows from each table so I can order by `num` descending?

This is the result I want:

Code: Select all

-----------------
|  name  |  num |
-----------------
|  derp  |  10  |
|  cerp  |  20  |
|  herp  |  30  |
|  ferp  |  40  |
|  werp  |  50  |
|  yerp  |  60  |
-----------------
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MYSQL] Combining rows from different tables

Post by califdon »

That can be done with a Union query. But the first question is why you have this data in 2 tables to begin with? Are these tables coming from different sources? If they are part of your database design, it's probably wrong to begin with.
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

Re: [MYSQL] Combining rows from different tables

Post by lenton »

I have two tables for my forums database structure. One is for threads and the other is for replies. I have separated these because the thread needs to hold lots of data which the reply doesn't. For example: the reply doesn't require a 'views' column whereas a thread does.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MYSQL] Combining rows from different tables

Post by califdon »

lenton wrote:I have two tables for my forums database structure. One is for threads and the other is for replies. I have separated these because the thread needs to hold lots of data which the reply doesn't. For example: the reply doesn't require a 'views' column whereas a thread does.
I would guess that that's a poor database design, although without knowing the entire structure, I can't really tell. With a properly designed schema, Union queries are seldom needed.
Post Reply