Dude, I AM going to be inserting data!
- Deryck H
- http://www.comp-u-exchange.com
----- Original Message -----
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Deryck Henson" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "MySQL" <[EMAIL PROTECTED]>
Sent: Thursday, September 13, 2001 8:04 PM
Subject: Re: using LEFT JOINS
> 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
---------------------------------------------------------------------
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
