Multi-tables LEFT JOIN problem

[Marek Kotowski]

In MySQL online documentation there are some examples with
multi-tables left joins. But all of them are like this (taken from the
documentation):

SELECT ...
FROM table1
LEFT JOIN table2 on (table1.id = table2.id)
LEFT JOIN table3 on (table1.id2 = table3.id2)
LEFT JOIN table4 on (table1.id3 = table4.id3)

Looks pretty. But in every ON clause there is table1, the same which
is in FROM clause. In other words all tables in joins are related to
the same table (table1). I have different situation: table3 is related
to table2 but not to table1. table4 is related to table3 but not to
table1.

Problem: how to constructs joins with such relations? May be part of
the ON clauses content should be put into WHERE clause? But what is
the rule: logic rule or a thumb rule?

Thank you in advance.

Marek Kotowski
Warsaw

[Aggro]

Marek Kotowski wrote:

Looks pretty. But in every ON clause there is table1, the same which
is in FROM clause. In other words all tables in joins are related to
the same table (table1). I have different situation: table3 is related
to table2 but not to table1. table4 is related to table3 but not to
table1.

Does this work:

SELECT ...
FROM table1
LEFT JOIN table2 on (table1.id = table2.id)
LEFT JOIN table3 on (table2.id2 = table3.id2)
LEFT JOIN table4 on (table3.id3 = table4.id3);

[Marek Kotowski]

Aggro <sp**********@yahoo.com> wrote in message news:<vd**************@read3.inet.fi>...

Does this work:

SELECT ...
FROM table1
LEFT JOIN table2 on (table1.id = table2.id)
LEFT JOIN table3 on (table2.id2 = table3.id2)
LEFT JOIN table4 on (table3.id3 = table4.id3);

No. It gives only matching records. In other words
it works like an INNER JOIN.

Regards

Marek Kotowski
Warsaw

[Aggro]

Marek Kotowski wrote:

No. It gives only matching records. In other words
it works like an INNER JOIN.

create table table1(id int);
create table table2(id int, id2 int);
create table table3(id2 int, id3 int);
create table table4(id3 int);

insert into table1 values(1),(2),(4),(5),(7),(8);
insert into table2 values(1,21),(2,22),(5,25),(7,27);
insert into table3 values(21,31),(22,32),(27,37);
insert into table4 values(31),(37);

mysql> SELECT * FROM table1
-> LEFT JOIN table2 on (table1.id = table2.id)
-> LEFT JOIN table3 on (table2.id2 = table3.id2)
-> LEFT JOIN table4 on (table3.id3 = table4.id3);
+------+------+------+------+------+------+
| id | id | id2 | id2 | id3 | id3 |
+------+------+------+------+------+------+
| 1 | 1 | 21 | 21 | 31 | 31 |
| 2 | 2 | 22 | 22 | 32 | NULL |
| 4 | NULL | NULL | NULL | NULL | NULL |
| 5 | 5 | 25 | NULL | NULL | NULL |
| 7 | 7 | 27 | 27 | 37 | 37 |
| 8 | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+
6 rows in set (0.01 sec)

What kind of results do you need? I understood that you wanted all rows
from table 1, and matching rows from other tables.

[Marek Kotowski]

Aggro <sp**********@yahoo.com> wrote in message news:<a3***************@read3.inet.fi>...

What kind of results do you need? I understood that you wanted all rows
from table 1, and matching rows from other tables.

Thank you. Your code works fine. But in my RDB
I have to add additional filter for table4.
It has at least one field more, 'name' for example.
So I have to include in the SELECT table4.name = 'something';
Or - in other words - table4.id3 = 'an id for something';
Where to put it? In WHERE or - with an AND operator -
in ON clause and which one (both ways don't work)?

Regards

MK

[Aggro]

Marek Kotowski wrote:

Thank you. Your code works fine. But in my RDB
I have to add additional filter for table4.
It has at least one field more, 'name' for example.
So I have to include in the SELECT table4.name = 'something';
Or - in other words - table4.id3 = 'an id for something';
Where to put it? In WHERE or - with an AND operator -
in ON clause and which one (both ways don't work)?

If you want to have ONLY those rows where table4.name = 'something',
then you need to add this at the end of the query (after the joins)

where table4.name = 'something'

# So if we add to my previous example:
alter table table4 add name varchar(255);
update table4 set name='something' where id3=31;
update table4 set name='else' where id3=37;

# We would get this with our previous query:
+------+------+------+------+------+------+-----------+
| id | id | id2 | id2 | id3 | id3 | name |
+------+------+------+------+------+------+-----------+
| 1 | 1 | 21 | 21 | 31 | 31 | something |
| 2 | 2 | 22 | 22 | 32 | NULL | NULL |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | 5 | 25 | NULL | NULL | NULL | NULL |
| 7 | 7 | 27 | 27 | 37 | 37 | else |
| 8 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+-----------+

# And this would be our new query:
SELECT * FROM table1
LEFT JOIN table2 on (table1.id = table2.id)
LEFT JOIN table3 on (table2.id2 = table3.id2)
LEFT JOIN table4 on (table3.id3 = table4.id3)
WHERE table4.name='something';

# And this would be the result:
+------+------+------+------+------+------+-----------+
| id | id | id2 | id2 | id3 | id3 | name |
+------+------+------+------+------+------+-----------+
| 1 | 1 | 21 | 21 | 31 | 31 | something |
+------+------+------+------+------+------+-----------+
# I could also show you what would happen if you put that
# new condition into join-clause:

SELECT * FROM table1
LEFT JOIN table2 on (table1.id = table2.id)
LEFT JOIN table3 on (table2.id2 = table3.id2)
LEFT JOIN table4 on (table3.id3 = table4.id3
AND table4.name='something');

+------+------+------+------+------+------+-----------+
| id | id | id2 | id2 | id3 | id3 | name |
+------+------+------+------+------+------+-----------+
| 1 | 1 | 21 | 21 | 31 | 31 | something |
| 2 | 2 | 22 | 22 | 32 | NULL | NULL |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | 5 | 25 | NULL | NULL | NULL | NULL |
| 7 | 7 | 27 | 27 | 37 | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+-----------+

As you can see, we would get all rows for table1, but values for table4
would be null, unless the name='something'. We could use both, but AFAIK
that would be just stupid. And it would give us the same results as it
would with only using where-clause.