SELECT c.name, c.surname,
substring(
if ( max(concat(cont.date,'Conctact ',cont.date) >
max(concat(compl.date,'Complaint ',compl.date),
if (max(concat(cont.date,'Conctact ',cont.date) >
max(concat(act.date,' Action ',act.action,act.date),
max(concat(cont.date,'Conctact ',cont.date),
max(concat(act.date,' Action ',act.action,act.date)
),
if (max(concat(compl.date,'Complaint ',compl.date) >
max(concat(act.date,' Action ',act.action,act.date),
max(concat(compl.date,'Complaint ',compl.date),
max(concat(act.date,' Action ',act.action,act.date)
)
)
11 ) LastActionCommentComplaint
FROM contact c,
contact_table cont,
complaints_table compl,
actions_table act
WHERE c.id = cont.main_id
AND c.id = compl.main_id
AND c.id = act.main_id
GROUP BY c.id. c.name, c.surname
Did not run it vs a DB so expect some typing errors. However I hope you
get the meaning.
Cheers
/rudy
-----Original Message-----
From: Petre Agenbag [mailto:[EMAIL PROTECTED]
Sent: dinsdag 15 juli 2003 9:25
To: [EMAIL PROTECTED]
Subject: Find entry with max date.
Hi
I have a relational db with a couple of tables holding things like
comments and complaints and actions.
In the "main" table, I have the contact details.
What I'd like to do is to query the db with a php app and to display the
entries in the result set with a "last action/comment/contact" done one
"max date".
ie.
main_table:
id name surname date
1 john doe 2003-07-07
2 jane doe 2003-07-08
contact_table
main_id date
1 2003-07-09
1 2003-07-12
2 2003-07-09
complaints_table
main_id date
1 2003-07-10
2 2003-07-11
actions_table
main_id action date
1 action1 2003-07-13
Now, querying the db for all the entries in main, i'd like to return
something like:
Name Surname Last action/comment/complaint
John Doe action:action1 2003-07-13
Jane Doe complaint: 2003-07-11
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
