asked on

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

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!

ASKER CERTIFIED SOLUTION

elfe69

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drews1f

ASKER

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

drews1f

ASKER

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!

elfe69

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";