Hi, Try as I might, I can't seem to work out the SQL for this query. Can you help please?
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> But what I keep getting all messed up is the joining up in between. I think I need to INNER JOIN to the intersection table, but how to then add the joins to the other relevant tables. Can you help please? Thanks in advance. Kyle --------------------------------------------------------------------- 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
