MySQL Forums
Forum List  »  General

Re: subquery select first row related to left join
Posted by: Andrew Gilfrin
Date: July 22, 2005 07:08AM

Some of the column names are a little different but this will work in version 4.1 and above....

mysql> select j.id as jobid, j.job, c.c_date, c2.what from jobs j LEFT JOIN
-> (select jobid, min(c_date) as c_date from calender group by jobid) c ON j.id = c.jobid
-> LEFT JOIN calender c2 ON (c.c_date = c2.c_date and c.jobid = c2.jobid)
-> order by 1 asc;

+-------+----------------------+---------------+--------------------------+
| jobid | job | c_date | what |
+-------+----------------------+---------------+--------------------------+
| 1 | campaign website | 2005-07-22 | consult campaign manager |
| 2 | recruit fundraiser | 2005-07-30 | place advert on website |
| 3 | prepare induction | NULL | NULL |
+-------+----------------------+------------+--------------------------+
3 rows in set (0.02 sec)

Andrew Gilfrin
------------------
http://gilfster.blogspot.com
My MySQL related Blog

http://www.mysqldevelopment.com
MySQL Stored Procedure,Trigger, View.... (Just about most things these days) Information

Options: ReplyQuote


Subject
Written By
Posted
Re: subquery select first row related to left join
July 22, 2005 07:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.