try something like this: select site_id, max(service_date), name from site_service left join site on site.id = site_service.site_id group by site_id;
--- Tom Beidler <[EMAIL PROTECTED]> wrote: > Iım having problems with a left join. Iım not even > sure if I should be using > a left join. > > I have two tables; sites and site_service. > Site_service contains service > dates for the sites. Iım trying to write a query > that will select each site > and the last service date for that site. A site may > have many service dates > or none at all. Hereıs what Iıve tried so far but it > pulls down all of the > service dates for a site if it has more then one and > I only want the last > date. > > SELECT DISTINCT site.id, site.site_id, site.name, > site_service.service_date, > site.service_order FROM site LEFT JOIN site_service > ON site.id = > site_service.site_id WHERE site.company = 'company' > ORDER BY > site_service.service_date DESC > > When I run the above I retrieve every service date > for a given site if it > has more then one. If I remove the > site_service.service_date from the SELECT > portion it retrieves only one site but I need at > least the latest date so > that doesnıt help. > > Any help appreciated. > > Tom > __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
