RE: How to implement this query - Can you help pls?
Roger, Thanks for the reply. That certainly doesn't give any errors but 'NATURAL JOIN' also unfortunately doesn't work. If I just take the first section; SELECT P.prodID, P.prodName FROM categories C NATURAL JOIN products P; I get an empty set. However; SELECT P.prodID, P.prodName FROM categories C LEFT JOIN products P ON P.catID = C.catID; I get what I would expect (i.e. a full list of prodNames). It has however given me more or less an idea of what I need to do. If it makes any difference to the NATURAL JOIN, I'm using "3.23.51-nt". Cheers -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 16:48 To: [EMAIL PROTECTED] Cc: Kyle Lange Subject: Re: How to implement this query - can you help pls? * 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. 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
RE: How to implement this query - can you help pls?
-Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 16:48 To: [EMAIL PROTECTED] Cc: Kyle Lange Subject: Re: How to implement this query - can you help pls? * 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. 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
Re: How to implement this query - can you help pls?
* 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. 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
Re: How to implement this query - can you help pls?
Kyle Lange wrote: 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 = AND PE.perID = 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 = AND PE.perID = 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 = AND PE.perID = ; 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
