[geeks] MySQL complex query.

[Mark Benson]

Got a real 'interesting' MySQL problem here. One of our systems stores email
records and notes for orders in separate MySQL tables.

emails looks like this:

kEmail | kOrder | <some data columns> | sent

notes looks like this:

kNote | kOrder | <some data columns> | date

the 'k' column is a Primary Key. The 'sent' and 'date' columns are both ISO
dates (YYYY-MM-DD HH:MM:SS).

What I need is to retrieve all these into one list of communications listed in
date order recent first. The common retrieval factor is 'kOrder' (the order
number) but otherwise no link is existent between the records in the two
tables.

 I got the tables to join, resulting in a single result set using:

SELECT *
FROM emails
     LEFT JOIN notes
        ON emails.kOrder = notes.kOrder
UNION
SELECT *
FROM emails
     RIGHT JOIN notes
        ON emails.kOrder = notes.kOrder

That gives on fat result containing all the columns form both tables and
separate records (columns are just NULL where not present in one or other
table.

Where I'm stuck is I don't know where to specify the select criteria (common
to both tables) or how to get them to sort by date with 2 different fields. Is
there maybe a way of copying the different date fields to a new column that is
common to all records, like 'absolute_date' or somesuch using SELECT AS?

--
Mark Benson

http://markbenson.org/blog