?optional? Join with 3 tables

[saltwater]

Here are the tables:

CREATE TABLE mmart_update (
id int(32) NOT NULL auto_increment,
contractid int(32) NOT NULL default '0',
omschrijving varchar(255) NOT NULL default '',
referentie varchar(128) NOT NULL default '',
aantalmin int(5) NOT NULL default '0',
masterid int(3) NOT NULL default '0',
statusid int(3) NOT NULL default '0',
startdate int(15) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;

CREATE TABLE mmart_udc (
id int(3) NOT NULL auto_increment,
client_id int(3) NOT NULL default '0',
aantalmin int(6) NOT NULL default '0',
chk_new int(1) NOT NULL default '0',
chk_cor int(1) NOT NULL default '0',
chk_tel int(1) NOT NULL default '0',
datum int(15) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;

CREATE TABLE mmart_clients (
id int(3) NOT NULL auto_increment,
name varchar(64) NOT NULL default '',
email varchar(128) NOT NULL default '',
username varchar(64) NOT NULL default '',
password varchar(64) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY name (name),
UNIQUE KEY username (username),
UNIQUE KEY id (id),
UNIQUE KEY email (email)
) TYPE=MyISAM;

Now, what I want to do is what the query below does, but I also want to get the records
who don't have records in mmart_udc and mmart_update, only in mmart_clients.
So if there is no corresponding record in mmart_update and/or mmart_udc, the record should also be in the recordset.

I am new to joins so forgive me if I take a wrong approach to this problem, any tips are welcome!

Thanks in advance,
Bruno

SELECT
mmart_clients.id,
mmart_clients.name,
mmart_clients.email,
COUNT(mmart_update.id) as updatecount,
SUM(mmart_update.aantalmin) as totalmin,
mmart_udc.aantalmin
FROM
mmart_clients,
mmart_udc,
mmart_update
WHERE
mmart_udc.client_id=mmart_clients.id AND mmart_update.contractid=mmart_udc.id
GROUP BY
mmart_clients.id

[[deleted]]

read the mysql manual about LEFT JOIN

[saltwater]

ok thx,
But can you do a left join with three tables?

Thx,
Bruno

[[deleted]]

Sure:

SELECT * FROM table1 LEFT JOIN table2 ON foo LEFT JOIN table3 ON bar

[saltwater]

Ok, now the query is correct and does (less or more what he is supposed to, but I don't understand why this is working.

Could you please explain me? what exactly does a LEFT JOIN, why is there a LEFT and RIGHT join
(what's so left or right about it? 🙂

Thanks again Vincent!

SELECT
mmart_clients.id,
mmart_clients.name,
mmart_clients.email,
COUNT(mmart_update.id) as updatecount,
SUM(mmart_update.aantalmin) as totalmin,
mmart_udc.aantalmin
FROM
mmart_clients LEFT JOIN mmart_udc ON mmart_udc.client_id=mmart_clients.id LEFT JOIN mmart_update ON mmart_update.contractid=mmart_udc.id
GROUP BY mmart_clients.id

[[deleted]]

A LEFT JOIN differs from a 'normal' JOIN in that a LEFT JOIN will allways add the columns from the RIGHT table to the LEFT, even if there are no records in the RIGHT table that match the queries requirements.

If you do

SELECT * FROM table1 LEFT JOIN table2 ON foo=bar

then the results will always contain all columns of both table1 and tale2, but the columns of tabl2 will contain NULL if there was no match on 'foo=bar'