wow!
that's known as "above and beyond the call of
duty". hope the newbies appreciate your work.
> 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!!
>
>
>
--
John Ragan
[EMAIL PROTECTED]
http://www.CoreReader.com/
---------------------------------------------------------------------
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
