Here's a mini-tutorial on left join that should solve your problem.
First, let's make a smaller example. (It would have been helpful if
you had done that and formatted your select so it could be read when
you posted the question ;-) Here are 2 tables:
select * from header; select * from placement;
+------+ +------+-----------+
| code | | code | product |
+------+ +------+-----------+
| 6 | | 6 | NOSUCH |
| 7 | | 7 | OVRLCKBDG |
| 8 | | 7 | FUBAR |
+------+ +------+-----------+
You want:
Info for header 6, and, if any, info for placement (6, '1029106')
Info for header 7, and, if any, info for placement (7, 'OVRLCKBDG')
Let's look at what a left join is. For a SQL query,
select A.*, B.* from A left join B on <condition>;
returns two things,
(1) Matching rows from A and B, as would be returned by
SELECT A.*, B.* from A, B where <condition>;
(2) Rows of the form A.*, null, ..., null where those rows
in A are included that do NOT participate in the match
in (1), followed by nulls representing the columns of B.
So, in our example,
select header.*, placement.*
from header
left join placement
on header.code = placement.code;
gives us all of the header codes and the matching placements:
+------+------+-----------+
| code | code | product |
+------+------+-----------+
| 6 | 6 | NOSUCH |
| 7 | 7 | OVRLCKBDG |
| 7 | 7 | FUBAR |
| 8 | NULL | NULL |
+------+------+-----------+
Well, we don't want all of the matching placements; we just want the
specific matches listed above. So, let's add these to the left
join condition:
select header.*, placement.*
from header
left join placement
on header.code = placement.code
and ( header.code = 6 and placement.product = '1029106'
or header.code = 7 and placement.product = 'OVRLCKBDG' );
This gives us the placements we want, but there are too many headers:
+------+------+-----------+
| code | code | product |
+------+------+-----------+
| 6 | NULL | NULL |
| 7 | 7 | OVRLCKBDG |
| 8 | NULL | NULL |
+------+------+-----------+
So. finally, we get rid of the extra headers by adding a where clause:
select header.*, placement.*
from header
left join placement
on header.code = placement.code
and ( header.code = 6 and placement.product = '1029106'
or header.code = 7 and placement.product = 'OVRLCKBDG' )
where header.code in (6, 7);
This gives the desired result:
+------+------+-----------+
| code | code | product |
+------+------+-----------+
| 6 | NULL | NULL |
| 7 | 7 | OVRLCKBDG |
+------+------+-----------+
====================
This is a bit ugly, in that the header.code's are listed twice, and there
are a lot of literals in the select statement. It might be better to
use a temporary table to hold the desired (code, product) pairs. Then,
we join the 3 tables with a select that seems less complex:
Here's the temporary table:
select * from tt;
+------+-----------+
| code | product |
+------+-----------+
| 6 | 1029106 |
| 7 | OVRLCKBDG |
+------+-----------+
and here's the 3-table join:
select header.*, placement.*
from header
inner join tt
on header.code = tt.code
left join placement
on tt.code = placement.code
and tt.product = placement.product;
which also gives the desired result:
+------+------+-----------+
| code | code | product |
+------+------+-----------+
| 6 | NULL | NULL |
| 7 | 7 | OVRLCKBDG |
+------+------+-----------+
> From: "Mark Colvin" <[EMAIL PROTECTED]>
> To: "MySQL Mailing List \(E-mail\)" <[EMAIL PROTECTED]>
> Subject: MySQL Left Join Query
> Date: Mon, 18 Nov 2002 08:05:51 -0000
>
> I have a two table query where I want to return records from the first
table
> and if they have corresponding details in my second table I wish to return
> those as well. A sample sql statement I am using is as follows:
>
> select decheader.code, decheader.height, decheader.width,
> decplacement.position, decplacement.product from decheader left join
> decplacement on decheader.code = decplacement.code where (decheader.code =
> '00007' and decplacement.product = 'OVRLCKBDG') or (decheader.code =
'00006'
> and decplacement.product = '1029106') or (decheader.code = '00005' and
> decplacement.product = '1029103') or (decheader.code = '00005' and
> decplacement.product = '1029104') or (decheader.code = '00005' and
> decplacement.product = '1029105') or (decheader.code = '00005' and
> decplacement.product = '1029106') or (decheader.code = '00004' and
> decplacement.product = '1029104') or (decheader.code = '00004' and
> decplacement.product = '1029105')
>
> In the above example, all of the codes exist in the decheader table but
only
> code 00007 has a corresponding product in the decplacement table. I would
> wish to return all the decheader details for the above codes and the
> decplacement details for code 00007 as well. This statement actually only
> gives me the details for code 0007 and nothing else. I should add that for
> each record in the decheader table, there may be one, none or many
recordsh
> in the decplacement table but I will only be interested in a specific one.
I
> hope this makes some sort of sense!!
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
