Mysql – How to join 3 tables together given 2 many-to-many relationships tables in between

join;MySQL

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?

Best Answer

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

Related Solutions

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Efficient way to join two tables with one to many relationship

Most effective way is to use an INNER JOIN, like this:

SELECT   *
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId

And when you need aggregates on child level, you can add a GROUP BY and SUM and/or AVG or other aggregate functions:

SELECT      P.Name
            , P.Address
            , SUM(C.Amount)
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId
GROUP BY    P.Name
            , P.Address

Best Answer

Related Solutions

Mysql – get column from too many tables in thesql

Efficient way to join two tables with one to many relationship

Related Question