MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Kyle Lange Date:March 14 2003 3:09pm
Subject:RE: How to implement this query - Sorry about the receipt folks
View as plain text  
Sorry people. I switched the receipt off originally, but the filter on
the server sent it back and I just replied forgetting the receipt 2nd
time round.

Apologies.

Kyle

-----Original Message-----
From: Kyle Lange [mailto:kyle_lange@stripped] 
Sent: Friday, 14 March 2003 15:46
To: mysql@stripped
Subject: How to implement this query - can you help pls?



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

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