MySQL Forums
Forum List  »  Newbie

mulitple joins not quite right?
Posted by: cris lewis
Date: August 27, 2018 03:28AM

I have three tables:

Competitions - keyed on a unique meetID
Entry forms - keyed on a unique packID, includes reference field to meetID
Entries - keyed on unique athID, includes reference field to packID


So for any competition (meetID) we can have none or more entry forms (packID) and for each entry form we can have none or more entries (entryID).


I want to query all competitions after a certain date (startdate) and if they have any entries, an entry count or and entry count of zero if there are no entry forms or entries on entry forms.

The query I have so far is:

SELECT *, count(e.athID) as entries
FROM entry_meets m
left join entry_pack p on p.meetID = m.meetID
left join entry_entry e on p.packID = e.packID
where m.startdate > '2017-01-01'
group by m.meetID
order by m.startdate DESC

This does not return the competitions with no entry forms.

I'm relatively new to joins so I am probably missing something most of you will see straight away.

Nevertheless, I am going round in circles and google searches have not given me that breakthrough moment.

Anyone care to help?

Thanks.

Options: ReplyQuote


Subject
Written By
Posted
mulitple joins not quite right?
August 27, 2018 03:28AM


Sorry, only registered users may post in this forum.

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.