MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Kyle Lange Date:March 14 2003 5:55pm
Subject:RE: How to implement this query - can you help pls?
View as plain text  

-----Original Message-----
From: Roger Baklund [mailto:roger@stripped] 
Sent: Friday, 14 March 2003 16:48
To: mysql@stripped
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

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