SELECT from MULTIPLE tables (not JOIN)

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
manixrock
Forum Commoner
Posts: 45
Joined: Sun Jul 20, 2008 6:38 pm

SELECT from MULTIPLE tables (not JOIN)

Post by manixrock »

This might be easy, but google didn't help.

I need to select rows from 2 tables but not to join them (I need to append one to the other), I want to select them as if I selected them from the first, then selected them from the second, and do an ORDER BY then a LIMIT.

For example, 2 tables (and their columns):
- cats (name, age, grade)
- dogs (name, race, speed, grade)

I need to select the dogs and cats sorted by their grade. Something like:

Code: Select all

SELECT name FROM (cats + dogs) ORDER BY grade DESC LIMIT 10;
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: SELECT from MULTIPLE tables (not JOIN)

Post by jaoudestudios »

Joins are only done when there is a relationship between 2 tables (foreign keys).

As far as I can tell in your situation you will have to do 2 queries.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: SELECT from MULTIPLE tables (not JOIN)

Post by EverLearning »

Something like this?

Code: Select all

(
    SELECT name, grade 
        FROM dogs
)
UNION
(
    SELECT name, grade 
        FROM cats
)
ORDER BY grade DESC 
LIMIT 10
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: SELECT from MULTIPLE tables (not JOIN)

Post by jaoudestudios »

Really, I did not know that! Thanks.

Am going to read up on union tonight.
manixrock
Forum Commoner
Posts: 45
Joined: Sun Jul 20, 2008 6:38 pm

Re: SELECT from MULTIPLE tables (not JOIN)

Post by manixrock »

Thanks EverLearning!
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: SELECT from MULTIPLE tables (not JOIN)

Post by EverLearning »

Glad I could help.
Post Reply