results of 2 mysql tables ordered by date in the same list

drews1f
drews1f used Ask the Experts™
on
Hello,

I am trying to come up with a select statement to grab information from 2 identical (apart from table name) tables and output them in an ordered list. For example:

InjuredPets
---
ID      |      Type      |     Date_Found

1       |      Cat         |     01/02/2006
2       |      Dog        |     09/06/2006
3       |      Cat         |     11/08/2006


MissingPets
---

1       |      Hamster   |     02/01/2006
2       |      Snake        |    04/05/2007



Now i could do 2 seperate select statements but then record 1 of table2 is going to be the 4th record when it should be the first.

Can anyone suggest how I might do this? Cheers in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Use UNION:

(SELECT * FROM injuredpets) UNION (SELECT * FROM missingpets) ORDER BY date_found

Author

Commented:
Hi thanks for your quick reply.

When i try this:

      $debtlistq = "(SELECT * FROM mortgage) UNION (SELECT * FROM remortgage) WHERE mort_completed='Yes' ORDER BY mort_application_date DESC";

I get no results (but i no for a fact some records have mort_completed set to Yes)
but when i remove the WHERE statement like so:

      $debtlistq = "(SELECT * FROM mortgage) UNION (SELECT * FROM remortgage) ORDER BY mort_application_date DESC";

it works fine. :S do you have any idea what im doing wrong with regard to the WHERE part of the statement? Many thanks

Author

Commented:
its cool i figured it out. I just put the same WHERE X segment of the statement inside the SELECT brackets for both tables.

Many thanks for your help mate!

Commented:
You cannot put a WHERE clause on the UNION, you have to put your WHERE clause in the SELECT statements:

$debtlistq = "(SELECT * FROM mortgage WHERE mort_completed='Yes') UNION (SELECT * FROM remortgage WHERE mort_completed='Yes') ORDER BY mort_application_date DESC";

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial