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 = 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
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. 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
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. 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
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. 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
