-----Original Message-----
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Friday, 14 March 2003 16:48
To: [EMAIL PROTECTED]
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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
