* Kyle Lange
> Try as I might, I can't seem to work out the SQL for this query. Can you
> help please?
I'll try.
> I need to retrieve a list of products within a certain category with
> their relevant prices obtained from a specific (date-based) *valid*
> pricelist relevant to a particular person.
>
> TABLE: persons - perID
> TABLE: priceList - listID, validFrom, validTo
> TABLE: intPerPriceList - perID, listID
> TABLE: categories - catID
> TABLE: products - prodID, catID, prodName
> TABLE: priceListItems - prodID, listID, itemPrice
Something like this:
SELECT P.prodID, P.prodName, PLI.itemPrice
FROM categories C
NATURAL JOIN products P,
persons PE
NATURAL JOIN intPerPriceList IPPL
LEFT JOIN priceList PL ON
PL.listID = IPPL.listID AND
CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo
LEFT JOIN priceListItems PLI ON
PLI.prodID = P.prodID AND
PLI.listID = PL.listID
WHERE
C.catID = "$catID" AND
PE.perID = "$perID"
Some comments:
NATURAL JOIN means the join is performed on any/all columns with the same
name in the two joined tables.
Normally in a multitable SELECT, you select FROM one table, join another on
some condition, join another on some condition and so on. In this case, two
separate "threads" of joins are made; first categories and products(=P),
then persons, intPerPriceList and priceList(=PL), and finally the two are
'glued together' with the priceListItems table, which has a condition to
match both P.prodID and PL.listID.
<URL: http://www.mysql.com/doc/en/JOIN.html >
HTH,
--
Roger
---------------------------------------------------------------------
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
