[geeks] MySQL complex query.

[Mouse]

> kEmail | kOrder | <some data columns> | sent

> kNote | kOrder | <some data columns> | date

> What I need is to retrieve all these into one list of communications
> listed in date order recent first.

>  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

> Where I'm stuck is I don't know where to specify the select criteria
> (common to both tables)

I see two alternatives: either specify WHERE clauses on the two SELECT
statements, or write, basically (but this doesn't work - see below),

SELECT * FROM (
	SELECT * FROM emails LEFT JOIN notes USING (kOrder)
	UNION
	SELECT * FROM emails RIGHT JOIN notes USING (kOrder)
) WHERE ....

> or how to get them to sort by date with 2 different fields.

Take my suggestion and add an ORDER BY clause to it.

I just tried the above, and it doesn't quite work.  I had to do

SELECT * FROM (
	SELECT emails.kEmail, notes.kNote, emails.kOrder,
		emails.datafield1 AS emdata1,
		emails.datafield2 AS emdata2,
		...,
		notes.datafield1 AS ntdata1,
		notes.datafield2 AS ntdata2,
		...,
		emails.sent AS emsent,
		notes.date AS ntdate
			FROM emails LEFT JOIN notes USING (kOrder),
	UNION
	SELECT emails.kEmail, notes.kNote, notes.kOrder,
		emails.datafield1 AS emdata1,
		emails.datafield2 AS emdata2,
		...,
		notes.datafield1 AS ntdata1,
		notes.datafield2 AS ntdata2,
		...,
		emails.sent AS emsent,
		notes.date AS ntdate
			FROM emails RIGHT JOIN notes USING (kOrder)
) AS merged WHERE .... ORDER BY ....

though of course I don't know to what extent that is an idiosyncracy of
the implementation I was testing against.

You could also add an expression involving emails.sent and notes.date
to each of the inner SELECTs to get a merged date field, though you'll
have to decide what you want it to be if the dates for the two records
for a single kOrder are different.  Perhaps something like

	IF(emails.sent IS NULL, notes.date,
		IF(notes.date IS NULL, emails.sent,
			IF(notes.date > emails.sent,
				notes.date, emails.sent))) AS tstamp

/~\ The ASCII				  Mouse
\ / Ribbon Campaign
 X  Against HTML		mouse at rodents-montreal.org
/ \ Email!	     7D C8 61 52 5D E7 2D 39  4E F1 31 3E E8 B3 27 4B