snippetsqlMinor
How to join 3 tables together given 2 many-to-many relationships tables in between?
Viewed 0 times
tablestogetherjoinbetweenmanyhowrelationshipsgiven
Problem
I have 3 main tables:
and 2 many-to-many link tables in between:
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:
How can I join them all together?
Person, Publication and Conferenceand 2 many-to-many link tables in between:
person_publication and person_conferenceI 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?
Solution
Join
Edit:
You would want to do something like this:
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
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:
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_idThe 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_idCode Snippets
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_idSELECT 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_idContext
StackExchange Database Administrators Q#71361, answer score: 8
Revisions (0)
No revisions yet.