MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:March 15 2003 2:22pm
Subject:Re: How to implement this query - can you help pls?
View as plain text  
In article
<!~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAABSKJBKnwwki4jVBhkWDhesKAAAAQAAAAxuQBMRr9h0eKjOO3eU+xwQEAAAAA@stripped>,
"Kyle Lange" <kyle_lange@stripped> writes:

> 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?

Maybe the following is what you want:

SELECT P.prodID, P.prodName, PLI.itemPrice
FROM products P
INNER JOIN priceListItems PLI ON PLI.prodID = P.prodID
INNER JOIN priceList PL ON PL.listID = PLI.listID
INNER JOIN intPerPriceList IPPL ON IPPL.listID = PL.listID
WHERE CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo
  AND P.catID = <x>
  AND IPPL.perID = <y>


[Filter fodder: SQL query]
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