mysql - Inner Join across 3 tables with possible empty tables -

here have 3 tables. want pull people attended either game a, or game b or both. here venn diagram of i'd get:

i'd have unique list of people (no duplicates). started out using 2 inner joins, gives me inside circle 3 tables intersect (which makes sense).

so, join or combination should use pull data?

person

• id
• first
• last

gamea

• typeid
• checkin_time
• person_id

gameb

• typeid
• checkin_time
• person_id

query:

this have tried, returns people have attended both game , game b:

 select * person   inner join gamea on person.id = gamea.person_id   inner join gameb on person.id = gameb.person_id 

i able results desired using query well, i'm not sure if there better way accomplish this:

select * person, gamea, gameb person.id = gamea.person_id or person.id = gameb.person_id group person.id 

there few ways this. 1 make union subquery returning person_id both gamea , gameb perform inner join limit person only, or use in()` subquery:

select   distinct person.*   person   inner join (     select person_id gamea     union      select person_id gameb   ) attendees on person.id = attendees.person_id 

alternatively, in() subquery:

select   distinct person.* person  id in (   select person_id gamea   union   select person_id gameb ) 

it might faster either of though, union on outside. if have foreign key relationships defined necessary, therefore enforcing indexing, inner join twice , union results of those:

select p.* person p inner join gamea on p.id = gamea.person_id union  select p.* person p inner join gameb on p.id = gameb.person_id