Page 1 of 1

SELECT from MULTIPLE tables (not JOIN)

Posted: Mon Aug 04, 2008 12:14 pm
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;

Re: SELECT from MULTIPLE tables (not JOIN)

Posted: Mon Aug 04, 2008 12:25 pm
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.

Re: SELECT from MULTIPLE tables (not JOIN)

Posted: Mon Aug 04, 2008 12:28 pm
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

Re: SELECT from MULTIPLE tables (not JOIN)

Posted: Mon Aug 04, 2008 12:31 pm
by jaoudestudios
Really, I did not know that! Thanks.

Am going to read up on union tonight.

Re: SELECT from MULTIPLE tables (not JOIN)

Posted: Mon Aug 04, 2008 12:35 pm
by manixrock
Thanks EverLearning!

Re: SELECT from MULTIPLE tables (not JOIN)

Posted: Mon Aug 04, 2008 12:37 pm
by EverLearning
Glad I could help.