OK, first of all, brilliant and well thought out. One problem, every one of
my tables are EXACTLY the same. All I want to do is take the records from
all 15 of the tables where the username(column) in them is what I say. When
I try to do this, it gives me an error that says it's ambiguous. So I need
to merge all the tables (and I dont mean the MERGE statement) together to
create a sort of one big master table but it still knows what table each
record is from. Hopefully that clears things up a bit, and I do
appreciate that long yet excellent explanation on LEFT JOINS. Thank you and
please, if you have an idea of what I mean, please tell me an answer.
- Deryck H
- http://www.comp-u-exchange.com
----- Original Message -----
From: "Loyd Goodbar" <[EMAIL PROTECTED]>
To: "Deryck Henson" <[EMAIL PROTECTED]>
Sent: Thursday, September 13, 2001 6:13 PM
Subject: Re: using LEFT JOINS
On Thu, 13 Sep 2001 07:03:03 -0500, "Deryck Henson" <[EMAIL PROTECTED]>
wrote:
>Well, before I asked which joins to use and now I want to know how to use
>the LEFT JOINS. They would be quite useful on my site right now. Any help
>is appreaciated and dont say to read the manual cause Ive tried that and it
>doesnt give a good enough explanation. This will be very helpful when
>displaying my database tables.
>
>- Deryck H
>- http://www.comp-u-exchange.com
Say you want to see records from a "header" table that has 1 row per unique
value (say, purchase orders), and a "detail" table that has multiple records
per unique value in the header table (purchase order line items)...
HEADER table (po_num is unique key):
po_num,po_date,vendor
1001,'2001-01-01','ABC Corp'
1002,'2001-01-01','DEF Corp'
1003,'2001-01-01','GHI Corp'
DETAIL table (po_num is foreign key to HEADER)
po_num,po_line,qty,price_per
1001,10,1,1.00
1001,20,1,0.50
1002,10,3,4.50
1002,20,6,0.25
1002,30,1,5.00
Before we start: I work on at least 3 database systems on a daily basis:
MySQL, Microsoft SQL server, and IBM's UDB. I primarily work with UDB and
SQL
server, so some items herein may or may not work correctly with MySQL.
However, they ARE valid ANSI SQL statements.
Now you want to know how much the total PO is. You can't just read the
header
file, it doesn't have prices. You must join the header and detail files. The
sum query is shown far below, but please read through the join information
first.
In normal (ANSI) type joins, the first table you use in the FROM clause is
the
"left" or "leftmost" table, and other tables are to the "right" of it.
Imagine
your select statement on one long line, and you'll see the relationship
between "left" and "right" tables.
If you run this statement:
select * from header join detail on (header.po_num=detail.po_num)
you will get
po_num,po_date,vendor,po_num,po_line,qty,price_per
1001,'2001-01-01','ABC Corp',1001,10,1,1.00
1001,'2001-01-01','ABC Corp',1001,20,1,0.50
1002,'2001-01-01','DEF Corp',1002,10,3,4.50
1002,'2001-01-01','DEF Corp',1002,20,6,0.25
1002,'2001-01-01','DEF Corp',1002,30,1,5.00
In this sample, you get as many header hits as there are detail hits. This
is
because each row in the detail is matched to a row in the header. There are
2
PO 1001s in the detail table, so the query engine must return those. It
matches these to PO 1001 in the header table.
Now, what happened to PO number 1003? A regular join only gives you results
if
all tables in the select statement return rows. The detail table for PO 1003
returned NULL rows, so it was discarded.
If you want to see all the PO header records, regardless of whether detail
records exist, you use a left join. The left join says, "return data from
the
left table even if there is nothing to return in the right table. Given the
above modified query:
select * from header left join detail on (header.po_num=detail.po_num)
you will get
po_num,po_date,vendor,po_num,po_line,qty,price_per
1001,'2001-01-01','ABC Corp',1001,10,1,1.00
1001,'2001-01-01','ABC Corp',1001,20,1,0.50
1002,'2001-01-01','DEF Corp',1002,10,3,4.50
1002,'2001-01-01','DEF Corp',1002,20,6,0.25
1002,'2001-01-01','DEF Corp',1002,30,1,5.00
1003,'2001-01-01','GHI Corp',-,-,-,-
(The dashes represent null values.)
For practical purposes, LEFT and LEFT OUTER joins are the same.
Now, you see there's a problem with PO 1003, it doesn't have any line items.
You want a query to identify POs that don't have line items. You can use
this
query:
select a.po_num,a.po_date,a.vendor from header a exception join detail b on
(a.po_num=b.po_num)
you will get:
po_num,po_date,vendor
1003,'2001-01-01','GHI Corp'
You could also use these alternate queries:
select a.po_num,a.po_date,a.vendor from header a where a.po_num not in
(select
b.po_num from detail b where a.po_num=b.po_num)
select a.po_num,a.po_date,a.vendor from header a, detail b where
a.po_num=b.po_num and b.po_num is null
Whenever you want columns from a specific table (not using *), you must
specify which table to select from, if column names are identical in both
tables. I could have used "vendor" instead of "a.vendor" above, and the
query
engine knows that vendor is only in the header table, not detail.
Now, for the original premise above... to get the total value for each PO:
select a.po_num,a.po_date,a.vendor,sum(b.qty * b.price_per) as po_total from
header a left join detail b on (a.po_num=b.po_num) group by
a.po_num,a.po_date,a.vendor order by a.po_num
will give:
po_num,po_date,vendor,po_total
1001,'2001-01-01','ABC Corp',1.50
1002,'2001-01-01','DEF Corp',24.50
To summarize:
When you want data from more than one table, you must JOIN them. Joining
combines data from different tables based on common values. A REGULAR JOIN
("join") will give data from both tables only if both tables return data
from
the query. A LEFT JOIN or LEFT OUTER JOIN will always give data from the
left
table, and give either data or NULL values from the right table. Some
servers
also allow a RIGHT JOIN that works the opposite from the LEFT JOIN - always
give rows from the right table, but values or NULLS from the left table (you
might use this to find PO detail lines that have no header records - should
not happen, but it does). On some systems, an EXCPTION JOIN shows values in
the left table that have no matching rows in the right table. If you are
looking for data in one table that is not in the other, you can use
exception
joins, subselects, or compare the missing table value to NULL.
HTH,
Loyd
--
"How much would you pay for your life?"
"More than I would take to give it up."
[EMAIL PROTECTED] ICQ#504581 http://lgoodbar2.pointclark.net/
---------------------------------------------------------------------
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
