Re: Newbie --- JOINS

Hello Bob,

BobSharp wrote:
> Picture does not seem to have been carried in the message,  posts with attachment did not seem to get through either.
> So  hope the link works.
> 
> 
> Below is the ER diagram in an exercise I am trying to do.
> http://www.probowluk.co.uk/images/er_ECA_001.jpg
> 
> It's been going well so far,  and several ColdFusion pages written already.
> 
> Need to do one for a  Purchase Order Report   for  ...
>  -   given  SupplierCode
>  -   given StartDate and EndDate of Orders
> 
> My problem is in the CFquery  -   understanding what JOINS to use and in what order to use them.
> 

Which kind of join to write all depends on how inclusive you want your 
results to be. On a sheet of paper draw two overlapping circles (yes 
this is a Venn diagram). In the left circle, write "left", in the right 
circle write "right", and where they overlap write "inner".

Above each circle put the name of a table. From your diagram and your 
descriptoin is sounds like you wanted to link Suppliers and 
PurchaseOrders. Put "Suppliers" over the left circle and 
"PurchaseOrders" over the right.

If I assume that `Suppliers`.`SupplierCode` and 
`PurchaseOrders`.`SupplierCodefk` are the  same numbers then here is how 
to get your results.

Remember: a JOIN is a form of cross-product between two tables. If you 
have one row in one table and it matches to 3 rows in the other table, 
then the first table's data will be listed 3 times. This correct and 
proper behavior for a database.

To get the list of all rows in Suppliers and any matching rows from 
PurchaseOrders, use this form:

SELECT ...
FROM `Suppliers`
LEFT JOIN `PurchaseOrders`
   ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk`
WHERE ...

To get all of the rows from PurchaseOrders and only those matching rows 
from Suppliers, use:

SELECT ...
FROM `Suppliers`
RIGHT JOIN `PurchaseOrders`
   ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk`
WHERE ...

And if you only want to list rows from either table if they have at 
least one matching row in the other, use:

SELECT ...
FROM `Suppliers`
INNER JOIN `PurchaseOrders`
   ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk`
WHERE ...

The WHERE clause is optional. Of course, you will have to either supply 
a list of columns or use the * operator for the SELECT clause but this 
is the general shape of the command you will want to use.

-- 
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[email protected]