MySQL Lists are EOL. Please join:

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