3

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?

2
  • So, if a person had 3 publications and was in 7 conferences, you want 21 rows in the result? Jul 13 '14 at 11:54
  • yes. this is what I want
    – user666923
    Jul 14 '14 at 2:01
7

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
3
  • Thank you for pointing me to this direction. I am new to sql, how do I make them sub-queries in a from clause, could you please elaborate?
    – user666923
    Jul 14 '14 at 2:02
  • @user666923 answer has been updated to give example.
    – JRLambert
    Jul 14 '14 at 2:43
  • The query is fine but you don't really need subqueries for this. Jul 14 '14 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.