A bit of a hard one to describe so please bear with me...
I have a table structure that looks a bit like this...
_events _event_guests _guests
- event_id----------> - event_id
- event_name - guest_id--------> - guest_id
- etc etc - guest_name
- etc etc
So event_id is linked with guest_id through another table called _event_guests....
This allows me to attach multiple guests to multiple events...
All set up with a master / detail relationship...
This all works great....
What I need to do is pull the guest_id and / or guest_name from _guests table into _events table...
I need to do this so I can search for a specific guest and return all the events they are attending...
I have tried many variations on INNER JOIN but can't get one to work...
My existing SQL qeury looks like this...
SELECT
`_events`.`event_id`,
`event_name`,
`event_website`,
`event_location`,
`event_address`,
`event_postcode`,
`continent_id`,
`country_id`,
`state_id`,
`city_id`,
`event_start_date`,
`event_finish_date`,
`_events`.`organiser_id`,
`_organisers`.`organiser_name`,
`_organisers`.`organiser_website`,
`_organisers`.`organiser_logo`,
`_event_guests`.`guest_id`
FROM `_events`
INNER JOIN `_organisers` ON `_organisers`.`organiser_id`=`_events`.`organiser_id`
INNER JOIN `_event_guests` ON `_event_guests`.`event_id`=`_events`.`event_id`
As you can see I already have an INNER JOIN to get info from the _organisers table as well...
However it creates a couple of problems...
- The events list is now incorrect... It will not show an event if no guest is associated... Also if an event has multiple guests then it appears multiple times... I need the event list to show all events once regardless of whether they currectly have guests associated with them yet...
- When attempting to search for an event based on a guest name I get the following error... Unknown column '_events.guest_id' in 'where clause'
Any help will be greatly appreciated
EDIT: Changing the second INNER JOIN to a LEFT JOIN shows events that currently have no guests, but still shows events multiple times if that event has mulitple guests... One step closer <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3758&image=1&table=forumtopics' class='bbc_emoticon' alt=':)' />
EDIT: OK another step closer I hope... Added the following line which gets rid of my duplicate events showing up <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3758&image=2&table=forumtopics' class='bbc_emoticon' alt=':D' />
GROUP by `_events`.`event_id`
So the search problem still remain <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3758&image=3&table=forumtopics' class='bbc_emoticon' alt=':(' />
EDIT: OK I think I have it all sorted... Changed a line in commonfunctions.php...
if($table=="_events" && $field=="guest_id") return "`_events`.`guest_id`";
with this...
if($table=="_events" && $field=="guest_id") return "`_event_guests`.`guest_id`";
Haven't fully tests it yet, but it seems to work just fine...
Amazing how just asking the question forces you to re-think the problem in the first place <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3758&image=4&table=forumtopics' class='bbc_emoticon' alt=':)' />