MySQL three table query

Question

I'm having a bit of trouble getting this query to pull the data I was looking for.

I have three tables -

guest_list_parties,

guest_list_guests,

and guests.

SELECT guest_list_parties.note, guests.accepted, guest_list_guests.first_name, guest_list_guests.last_name, guest_list_guests.title, guest_list_parties.note
FROM guest_list_guests
INNER JOIN guest_list_parties ON guest_list_guests.party_id = guest_list_parties.id
INNER JOIN guests ON guests.guest_list_guest_id = guest_list_guests.id

The query here only returns records where there are entries in the 'guest' table.

I would like to return all records regardless of whether or not there is a linking entry in the 'guests' table.

That being said, would I use a left outer join, or a right outer join?

Answer 1

Use left join instead, but this will check if the data is available on guest_list_parties since its an inner join to that table.

SELECT guest_list_parties.note, 
guests.accepted, 
guest_list_guests.first_name, 
guest_list_guests.last_name,
guest_list_guests.title, guest_list_parties.note
FROM guest_list_guests
INNER JOIN guest_list_parties 
ON guest_list_guests.party_id = guest_list_parties.id
LEFT JOIN guests ON guests.guest_list_guest_id = guest_list_guests.id