On Mon 1999-09-20 00:13:46 -0500, Ryan Junster wrote:
> I am have a bit of a problem making this join work for me. I need to join
> three tables together with one containing client records, another
> containing comments about a client and the other being a lookup table.
> Now I want the following output.
> client_id Company Contact Last Event Description
> 4 Harris Corporation Jeanne Smith 09/01/1999 Emailed Received Bill
> 3 Maxell Corp Jack Flash 09/10/1999 Note Had Meeting
> 2 Target Corp Jane Doe 08/10/1999 Note Sent Invoice
> 1 Target Corp John Smith 08/10/1999 Called Sent Check
> The requirements are:
> Sorted by Company, then Contact.
> All records in table client are displayed, even if there is no
> record in Comments.
> The last record created in Comments is displayed for each Client.
> Last is formatted using date_format(last, 'd/m/Y')
> This be done in one query (if possible).
, DATE_FORMAT(co.Last,'%m/%d/%Y') AS Last
Client AS cl LEFT JOIN
Comments AS co ON cl.client_id=co.client_id
, Comments AS co2 LEFT JOIN
Events AS e ON co.event_id=e.event_id
GROUP BY cl.Company, cl.Contact, co.Last
The difficulties with this query are:
- "even if there is no record in Comments"
--> typical application of LEFT JOIN
- "The last record created in Comments is displayed for each Client"
--> it's not easy to get the maximum of Last and at the same time
the corresponding row
My trick here is to do a cross join with all other Last dates for
the client_id, and then count how often a given co.Last is smaller
then all the other co2.Last. Counting is done by SUMming up the
1s of true comparisons. If this count is zero, co.Last must be
a biggest one ...
For this query to be reasonable efficient, there must be indexes on
Client.client_id, Comments.client_id, Comments.event_id, and
Events.event_id. And there should be only a small number of comments
per client, because otherwise finding the newest comment using a cross
join will become quite inefficient.
For a bigger number of comments per client, it's certainly better to
find the newest dates first in a prelimiary step, and then use this
information in the actual query.
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7
|• Help with joins||Junster||20 Sep|
| • Re: Help with joins||Martin Ramsch||21 Sep|