I'm trying to use query builder for this, but if I have to go straight sql I'll live with it!
That's why I'm posting here in CI forums and not just in a mysql forum.
I am trying to figure out the best way to solve this in a single query to avoid making additional data calls.
I have 5 tables involved:
event group (EG) - for all groups of events (egid, title)
events (EV) - individual events, connected to EG using EG ID (evid, title, date)
members (MEM) - individual members that will belong to EG's (many to many using MG) (memid, name etc)
memgroup (MG) - connects MEM to EG using MEM ID and EG ID (mgid, memid, egid)
results (RS) - results from the events, connected to MEM by MEM ID (memid, evid, points etc)
What I am stuck on is I have a form to fill in results, that is based on the single EV ID
I can grab all the MEMs that belong to the EG that the EV belongs to using a MEM left join to MG, no problem.
what I cant figure out is how to only show the MEMs that don't have RS's for that EV so the form can have a list of of just non-entered MEM results for this event to make it easier for non-techies to fill out and not screw it up with duplicated entries etc. or scrolling threw 50 names for every entry.
Basically select members that are are in group that this event belongs to that have not had results posted yet, by passing just the event ID
Or maybe there is a competely different way to approach this, I can't think of one haha!