MySQL Forums
Forum List  »  Newbie

Re: mulitple joins not quite right?
Posted by: Peter Brawley
Date: August 27, 2018 09:51AM

> SELECT *, count(e.athID) ... group by m.meetID

The "*" will return arbitrary results for all columns except meetid and athid. Read the manual page on aggregation.

> ... FROM entry_meets m
> left join entry_pack p ... where ... p.status='Accepted' ...

The Where condition on p.status effectively turns the left join into an inner join. To avoid that, write ...

select m.meetid, ifnull( p.pcount, 0 ) as entrypacks
from entry_meets m
left join (
  select meetid, count(*) as pcount
  from entry_pack
  where p.status='Accepted'
) on p.meetID = m.meetID

> get all the meets with entry counts (from entry forms that have been accepted) including meets with no entry forms?

Options: ReplyQuote

Written By
Re: mulitple joins not quite right?
August 27, 2018 09:51AM

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.