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 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 = 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
My MySQL related Blog
MySQL Stored Procedure,Trigger, View.... (Just about most things these days) Information

Options: ReplyQuote

Written By
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.