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/mysql?unsub=arch...@jab.org
