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
I have the basics;
SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE,
intPerPriceList IPPL, categories C .......... < I can't figure this bit
out>........ WHERE PL.validFrom < CURRENT_DATE AND PL.validTo >
CURRENT_DATE AND C.catID = <x> AND PE.perID = <y>
You can use the syntax you started out with, as long as you add some join conditions. It looks to me as though all joins involved are inner joins. Also, I don't think you actually need to retrieve anything from categories -- you have that information in products already. Given that, try:
SELECT P.prodID, P.prodName, PLI.itemPrice
FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL
WHERE PL.validFrom < CURRENT_DATE AND PL.validTo > CURRENT_DATE AND P.catID = <x> AND PE.perID = <y>
AND PE.perID = IPPL.perID /* join persons to intPerPriceList */
AND IPPL.listID = PL.listID /* to priceList */
AND IPPL.listID = PLI.listID /* to priceListItems */
AND PLI.prodID = P.prodID /* to products */
;
Alternatively, if you want to use the newer INNER JOIN syntax, we just rearrange it and modify it a bit:
SELECT P.prodID, P.prodName, PLI.itemPrice
FROM persons PE INNER JOIN intPerPriceList IPPL ON PE.perID = IPPL.perID
INNER JOIN priceList PL ON IPPL.listID = PL.listID
INNER JOIN priceListItems PLI ON IPPL.listID = PLI.listID
INNER JOIN products P ON PLI.prodID = P.prodID
WHERE PL.validFrom < CURRENT_DATE AND PL.validTo > CURRENT_DATE
AND P.catID = <x> AND PE.perID = <y>
;Also, I suspect that you wanted ">=" and "<=" in your date comparisons above.
Bruce Feist
PS: Did you know that if you don't include one of the following words:
sql,query,queries,smallint
your message won't get posted here?
BF
--------------------------------------------------------------------- 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
