Re: Help with joins - Mr. Anthony R.J. Ball - com.mysql.lists.mysql

4 messages in com.mysql.lists.mysqlRe: Help with joins

From	Sent On	Attachments
Junster	19 Sep 1999 22:13
Martin Ramsch	20 Sep 1999 19:35
Junster	21 Sep 1999 08:15
Mr. Anthony R.J. Ball	21 Sep 1999 09:38

Subject:	Re: Help with joins
From:	Mr. Anthony R.J. Ball (an...@maine.com)
Date:	09/21/1999 09:38:09 AM
List:	com.mysql.lists.mysql

SELECT A.client_id,A.company,A.contact,B.last,C.event,B.description FROM Client AS A LEFT JOIN Comments AS B ON A.client_id = B.client_id LEFT JOIN Events AS C ON B.event_id = C.event_id ORDER BY A.company, A.contact;

There is documentation on the wesite as to how to format the date the way you want it.

I think there is aa way to get the most recent comment out of this... but right now I'm not sure. It could always be pulled out in code.

Here is an abreviated view of the three tables.

Client Comments Events ------------- --------------- ------------- client_id int(6) comment_id int(6) event_id int(6) company char(30) client_id int(6) event char(20) contact char(45) event_id int(6) etc.. description text last timestamp

Here is some example data

Client

------ 1 Target Corporation John Smith 2 Target Corporation Jane Doe 3 Maxwell Corporation Jack Flash 4 Harris Corporation Jeanne Smith 5 Smith Corportion John Smith

Comments

--------------- 1 1 3 Received Invoice 19990809122340 2 2 4 Sent Invoice 19990810123213 3 4 1 Received Bill 19990901233212 4 1 3 Sent Check #1323 19990810122312 5 3 4 Had Meeting 19990910121231

Events

------ 1 Emailed 2 Received Email 3 Called 4 Note 5 Received Call

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 5 Smith Corp John Doe NULL NULL NULL 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).

Any help would be appreciated.

Thanks Ryan

--------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail mysq...@lists.mysql.com

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail mysq...@lists.mysql.com instead.