How to join 3 tables together given 2 many-to-many relationships tables in between? I have 3 main tables: Person, Publication and Conference

and 2 many-to-many link tables in between: person_publication and person_conference

I want to join all 3 main tables together (showing me all the publication and conference a person was involved in)

But so far, I am only able to query either person-to-publication or person-to-conference like this:

select person.*, publication.*
from person
left join person_publication on person_publication.person_id = person.id
left join publication on person_publication.publication_id = publication.id;


select person.*, conference.*
from person
left join person_conference on person_conference.person_id = person.id
left join conference on person_conference.conference_id = conference.id;


How can I join them all together?
Join conference to person_conference, selecting the person key from person_conference. Do the same for publication and person_publication. Make the two queries sub queries in a from clause and join the two on the person key.

Edit:

You would want to do something like this:

SELECT 
    p.person_id, 
    p.name, 
    a.conference, 
    b.publication
FROM
    person AS p
    LEFT JOIN (SELECT
                   pc.person_id,
                   c.conference
               FROM
                   person_conference AS pc
                   INNER JOIN conference AS c 
                     ON pc.conference_id = c.conference_id) AS a
      ON p.person_id = a.person_id 
    LEFT JOIN (SELECT
                   pp.person_id,
                   ppp.publication
               FROM
                   person_publication AS pp
                   INNER JOIN publication AS ppp 
                     ON pp.publication_id = ppp.publication_id) AS b
      ON p.person_id = b.person_id


The inner queries are called sub queries. These will act as a "table" (they are not the same) that you can then query against. If you intend to use sub-queries as a "table" though, you do need to alias them using the AS key-word.

EDIT:

I have learned a lot since 2014 and the query above shows that. I am leaving it as a reference for the answer that was given but the following would be a better query:

SELECT per.person_id, 
    per.name, 
    conf.conference, 
    pub.publication
FROM person AS per
    LEFT JOIN person_conference AS pconf
        ON per.person_id = pconf.person_id
    LEFT JOIN conference AS conf
        ON pconf.conference_id = conf.conference_id
    LEFT JOIN person_publication AS ppub
        ON per.person_id = ppub.person_id
    LEFT JOIN publication AS pub
        ON ppub.publication_id = pub.publication_id