Hi, I don't really have an answer for you but some things you could do to help people help you is:
- Format your SQL nicely, here is a great tool to do that: http://www.dpriver.com/pp/sqlformat.htm - Provide table structures and dummy data when asking questions about SQL - Post your question on http://stackoverflow.com/ David Neilsen | 07 834 3366 | PANmedia ® On Fri, Jul 8, 2011 at 3:36 PM, Jochen Daum <[email protected]> wrote: > Hi all, > > I have a query reading a date from a parent/child relationship and i > need to group the records by the max of the date, from whichever > tables this comes. My query: > > select > child.id as id, max(pm.start), max(membership.start), > max(ifnull(pm.start,membership.start)) as start from organisation > child > left join organisation pmorg on child.parent_organisation_id=pmorg.id > left join membership on child.id=membership.organisation_id > left join membership pm on pmorg.id=pm.organisation_id > > where child.id in (1,3) and /*just look at example records*/ > > > coalesce(pm.start,membership.start) is not null and > ((membership.active=1 and membership.amount_paid > 0.00 ) or > (pm.active=1 and pm.amount_paid > 0.00)) > > group by child.id > > > > My Output is strangely: > > > 1 NULL 2011-07-01 00:00:00 > 323031312d30372d30312030303a30303a3030 > 3 2011-07-01 00:00:00 NULL > 323031312d30372d30312030303a30303a3030 > > > > I need that last column to be 2011-07-01 and I need both rows. Any > idea why max(coalesce()) doesn't work? I'm running MySQL 5.1 > > > > > Kind Regards, > > Jochen Daum > > "There is no shortcut to anywhere worth going" - Beverly Sills > > P.S.: Missed our newsletter? http://eepurl.com/ejRsg > > Automatem Ltd > Phone: 09 630 3425 > Mobile: 021 567 853 > Email: [email protected] > Website: www.automatem.co.nz > http://nz.linkedin.com/in/automatem > http://twitter.com/automatem > > -- > NZ PHP Users Group: http://groups.google.com/group/nzphpug > To post, send email to [email protected] > To unsubscribe, send email to > [email protected] > -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
