MySQL Lists are EOL. Please join:

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

Thanks for the reply. That certainly doesn't give any errors but
'NATURAL JOIN' also unfortunately doesn't work. 

If I just take the first section;
SELECT P.prodID, P.prodName FROM categories C NATURAL JOIN products P;
I get an empty set. However;
SELECT P.prodID, P.prodName FROM categories C LEFT JOIN products P ON
P.catID = C.catID;
I get what I would expect (i.e. a full list of prodNames). It has
however given me more or less an idea of what I need to do. 

If it makes any difference to the NATURAL JOIN, I'm using "3.23.51-nt".

Cheers


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