At 7:55 PM -0500 9/13/01, Deryck Henson wrote:
>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
Actually, you probably do want a MERGE statement. Or rather, a MERGE
table. Your reason for not using one is that it doesn't support INSERT.
I asked what that had to do with anything, because when you want to update
one of these fifteen tables, you'll clearly want to put the row back in
the table from which it originally came, and therefore must know which
table that is. (If you don't know, that means you can't distinguish them
and then there's no reason to have 15 tables in the first place.) I'm
still unclear why a MERGE table won't work. LEFT JOIN isn't what you
want.
>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
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
