Hi Jochen,
What is "323031312d30372d30312030303a30303a3030" in the output? Is it
what is supposed to be the result of
max(ifnull(pm.start,membership.start))
?
On 08/07/11 15:36, Jochen Daum 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]
