MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Bruce Feist Date:March 14 2003 3:29pm
Subject:Re: How to implement this query - can you help pls?
View as plain text  
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


Thread
How to implement this query - can you help pls?Kyle Lange14 Mar
  • RE: How to implement this query - Sorry about the receipt folksKyle Lange14 Mar
  • sql diff for mysqlFlorent Martineau14 Mar
    • Re: sql diff for mysqlKaram Chand14 Mar
      • Re: sql diff for mysqlPaul DuBois14 Mar
    • Re: sql diff for mysqlTonu Samuel20 Mar
      • Re: sql diff for mysqlFlorent Martineau20 Mar
        • Re: sql diff for mysqlKaram Chand20 Mar
  • Re: How to implement this query - can you help pls?Bruce Feist14 Mar
  • Re: How to implement this query - can you help pls?Roger Baklund14 Mar
    • RE: How to implement this query - can you help pls?Kyle Lange14 Mar
      • RE: How to implement this query - Can you help pls?Kyle Lange14 Mar
  • Re: How to implement this query - can you help pls?Harald Fuchs15 Mar
Re: sql diff for mysqlHeikki Tuuri20 Mar