List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 21 1999 2:36am
Subject:Re: Help with joins
View as plain text  
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).

SELECT
   cl.client_id
 , cl.Company
 , cl.Contact
 , DATE_FORMAT(co.Last,'%m/%d/%Y') AS Last
 , e.Event
 , co.Description
FROM
   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
WHERE
   co.client_id=co2.client_id
GROUP BY cl.Company, cl.Contact, co.Last
HAVING SUM(co.Last<co2.Last)=0;

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.

Regards,
  Martin
-- 
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
Thread
Help with joinsJunster20 Sep
  • Re: Help with joinsMartin Ramsch21 Sep