Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Left Joins to link three or more tables

MANY-TABLE JOINS IN MYSQL - BACKGROUND

Data held in SQL tables should be normalised - in other words, held in neat multiple tables with complete rows, only one piece of logical data per cell, and with information not being repeated in multiple places. (The "why" is off topic for this article, but it basically helps data maintenance and integrity no end).

Multiple normalised tables can be linked together within select commands and this linking is known as joining; when you specifiy a join, you also specify a criteria to tell MySQL how to make the connection, and that's typically done using a key. Let's see a simple example.

Two tables - bdg containing buildings ....

-------+-----+
| name | bid |
-------+-----+
| 404 | 1 |
| 405 | 2 |
-------+-----+

... and res containing residents living there.

---------+------+-----+
| person | bid | rid |
---------+------+-----+
| Graham | 1 | 101 |
| Lisa | 1 | 102 |
---------+------+-----+

When I connect (join) those tables together, I wish to do so by linking the "bid"s - and the syntax I use is:

select * from bdg, res where bdg.bid = res.bid ;

You'll notice that I DON'T use the word join (I could ... but that's another story). Here's my output:

-------+-----+--------+------+-----+
| name | bid | person | bid | rid |
-------+-----+--------+------+-----+
| 404 | 1 | Graham | 1 | 101 |
| 404 | 1 | Lisa | 1 | 102 |
-------+-----+--------+------+-----+

Which is good - in other words, it's what I expected. BUT ... it might be that I want to see at least one row on my report for each of the incoming rows in (say) my building table - to alert me to buildings that don't match any resident records at all. Than can be done using a LEFT JOIN in my select:

select * from bdg left join res on bdg.bid = res.bid ;
    
which gives:

-------+-----+--------+------+------+
| name | bid | person | bid | rid |
-------+-----+--------+------+------+
| 404 | 1 | Graham | 1 | 101 |
| 404 | 1 | Lisa | 1 | 102 |
| 405 | 2 | NULL | NULL | NULL |
-------+-----+--------+------+------+

THREE WAY JOINS

Regular joins and left joins can be extended to three and more tables - the principle is easy but the syntax less so; let's say that we had a third table called dom containing the names of any internet domains registered to each individual:

------------------------+------+-----+
| domain | rid | did |
------------------------+------+-----+
| www.grahamellis.co.uk | 101 | 201 |
| www.sheepbingo.co.uk | 101 | 202 |
------------------------+------+-----+

A regular join on the (now) three tables is straightforward:

select * from bdg, res, dom where bdg.bid = res.bid and res.rid = dom.rid;

and gives the following result:

-------+-----+--------+------+-----+-----------------------+------+-----+
| name | bid | person | bid | rid | domain | rid | did |
-------+-----+--------+------+-----+-----------------------+------+-----+
| 404 | 1 | Graham | 1 | 101 | www.grahamellis.co.uk | 101 | 201 |
| 404 | 1 | Graham | 1 | 101 | www.sheepbingo.co.uk | 101 | 202 |
-------+-----+--------+------+-----+-----------------------+------+-----+

The syntax for a three way LEFT JOIN is more complex (and thus the inspiration for this article):

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid;

and gives the following result:

-------+-----+--------+------+------+-----------------------+------+------+
| name | bid | person | bid | rid | domain | rid | did |
-------+-----+--------+------+------+-----------------------+------+------+
| 404 | 1 | Graham | 1 | 101 | www.grahamellis.co.uk | 101 | 201 |
| 404 | 1 | Graham | 1 | 101 | www.sheepbingo.co.uk | 101 | 202 |
| 404 | 1 | Lisa | 1 | 102 | NULL | NULL | NULL |
| 405 | 2 | NULL | NULL | NULL | NULL | NULL | NULL |
-------+-----+--------+------+------+-----------------------+------+------+

Notice that our report now includes orphan records at both join levels - entries in the bdg table that have no corresponding entry in the res table, and entries in the res table that have no corresponding entry in the dom table.

THREE WAY JOINS - LOOKING FOR INCOMPLETE RECORDS

Should we wish to report on orphan records only, we can do so by testing for NULL fields in fields that may not otherwise have a null value.

Example - looking for all incomplete records:

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL;

-------+-----+--------+------+------+--------+------+------+
| name | bid | person | bid | rid | domain | rid | did |
-------+-----+--------+------+------+--------+------+------+
| 404 | 1 | Lisa | 1 | 102 | NULL | NULL | NULL |
| 405 | 2 | NULL | NULL | NULL | NULL | NULL | NULL |
-------+-----+--------+------+------+--------+------+------+

Example - looking for all buildings with no residents:

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where res.rid is NULL;

-------+-----+--------+------+------+--------+------+------+
| name | bid | person | bid | rid | domain | rid | did |
-------+-----+--------+------+------+--------+------+------+
| 405 | 2 | NULL | NULL | NULL | NULL | NULL | NULL |
-------+-----+--------+------+------+--------+------+------+

(Hey - you really don't need to join in the domain table for this)

Example - looking for all residents with no domains:

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL and res.rid is not NULL;

-------+-----+--------+------+------+--------+------+------+
| name | bid | person | bid | rid | domain | rid | did |
-------+-----+--------+------+------+--------+------+------+
| 404 | 1 | Lisa | 1 | 102 | NULL | NULL | NULL |
-------+-----+--------+------+------+--------+------+------+

SUMMARY OF MYSQL COMMANDS USED

Here's a complete set of the commands I used to set up this example - you're welcome to cut and paste it for your own testing and experimentation:

use test;
drop table if exists bdg;
drop table if exists res;
drop table if exists dom;
create table bdg (name text, bid int primary key);
create table res (person text, bid int, rid int primary key);
create table dom (domain text, rid int, did int primary key);

insert into bdg values ("404",1);
insert into res values ("Graham",1,101);
insert into dom values ("www.grahamellis.co.uk",101,201);
insert into dom values ("www.sheepbingo.co.uk",101,202);
insert into res values ("Lisa",1,102);
insert into bdg values ("405",2);

select * from bdg;
select * from res;
select * from dom;

select * from bdg, res where bdg.bid = res.bid ;
select * from bdg, res, dom where bdg.bid = res.bid and
    res.rid = dom.rid;

select * from bdg left join res on bdg.bid = res.bid ;
select * from (bdg left join res on bdg.bid = res.bid)
    left join dom on res.rid = dom.rid;

select * from (bdg left join res on bdg.bid = res.bid)
    left join dom on res.rid = dom.rid where dom.rid is NULL;
select * from (bdg left join res on bdg.bid = res.bid)
    left join dom on res.rid = dom.rid where res.rid is NULL;
select * from (bdg left join res on bdg.bid = res.bid)
    left join dom on res.rid = dom.rid
    where dom.rid is NULL and res.rid is not NULL;


See also More (My)SQL commands

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

MySQL - General
  [2053] - ()
  [2085] - ()
  [2134] - ()
  [2240] - ()
  [2426] - ()
  [2559] - ()
  [2561] - ()
  [2567] - ()
  [2861] - ()
  [3361] - ()

More MySQL commands
  [158] - ()
  [159] - ()
  [279] - ()
  [449] - ()
  [494] - ()
  [502] - ()
  [513] - ()
  [515] - ()
  [517] - ()
  [567] - ()
  [572] - ()
  [581] - ()
  [591] - ()
  [673] - ()
  [1213] - ()
  [1235] - ()
  [1331] - ()
  [1574] - ()
  [1735] - ()
  [1904] - ()
  [2110] - ()
  [2259] - ()
  [2448] - ()
  [2643] - ()
  [2644] - ()
  [2645] - ()
  [2647] - ()
  [3061] - ()
  [3270] - ()
  [4481] - ()

SQL Primer as Used in MySQL
  [158] - ()
  [270] - ()
  [494] - ()
  [502] - ()
  [515] - ()
  [591] - ()
  [2240] - ()
  [3060] - ()
  [3061] - ()
  [4007] - ()

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

Comment: "I really appreciated your article. I'm using a MySQL ..."
Visitor Ranking 4.4 (5=excellent, 1=poor)
4 unpublished comments pending on this page

Comment by David Bernard (published 2011-08-18) Suggested link.
I really appreciated your article. I'm using a MySQL database to run the queries for my master's thesis. I didn't exactly use your technique, but you pointed me in the right direction (as is often the case with Internet articles). [#3970]

Comment by Anon (published 2011-05-24)
thank u so much. [#3952]

Comment by Peter (published 2011-03-19)
Thanks for a very clear and to the point article.
Just what I needed.
Great [#3930]

Comment by Neil Hendricks (published 2011-01-16)
Great article, really helpful [#3868]

Comment by João Galetto (published 2010-11-04)
Excellent! Thanks! [#3821]

Comment by Anon (published 2010-10-25)
Excellent tutorial with example [#3807]

Comment by Aditya Menon (published 2010-10-08) Suggested link.
Many thanks for the article Ellis, like others said, it has helped me add to my repository of MySQL Awesomeness.

I have a suggestion to make - could you please edit the column names so they make more sense? building_id is better than bid, for example... I know that might make it bit more tedious to type and maintain, and I also realize you can't complain when you're eating it free, but I'm sure you'd give it a thought :) [#3792]

Comment by David Ryder (published 2010-08-04) Suggested link.
Thanks! This piece taught me how to do a 3+ table join. Actually pretty easy after you write a couple queries. On to more advanced topics! :) [#3656]

Comment by benjie (published 2010-07-15)
thanks for educating me [#3651]

Comment by Graham (published 2010-05-18) Suggested link.
On "three letter acronymns" ... there's something of a compromise between keeping the code reasonably short so that you can see the structure, and including very long and descripting names to help those who's mother tongue perhaps isn't English. As we train in English, my target market for articles here has to be people who are fluent in that language, and I'm not going to obfurscate the pattern of the code by pumping up the comments to help a wider market that we can't practically service. [#3581]

Comment by Anon (published 2010-05-18)
The guide itself is pretty straight forward but really, these three letter abbreviations are slightly hard to follow. [#3552]

Comment by fandhie (published 2010-05-18)
very helpful.. after I tried many tutorial to joining 3 tables from several website I finally found this one and it solves my problem :)
so many thank you [#3492]

Comment by Anon delivers (published 2010-02-08)
This article finally made me understand SQL Joins! Thanks a lot :) [#3461]

You can Add a comment or ranking or edit your own comments
There are presently 4 new comment(s) pending publication on this page

Average page ranking - 4.4

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-le ... ables.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard