levels information?
Adam H
On Fri, 13 Aug 2004 16:50:25 -0500, Paul Giesenhagen
<[EMAIL PROTECTED]> wrote:
> I tried to ask this on Experts Exchange without any luck ... maybe one of you guys may be able to help out.
>
> I am trying to build some pricing levels into our application and having a hard time with SQL query, here is an explination.
>
> I have 2 tables:
>
> LEVELS
> ================
> levelID, code, description, status
>
> LEVELPRICING
> ================
> levelPriceID, levelID, productID, Price
>
> I am running the following query:
> =================================
> SELECT l.levelID, l.code, l.description, p.price
> FROM #request.levelTable# l
> RIGHT OUTER JOIN #request.levelPricingTable# p
> ON l.levelID = p.levelID
> WHERE (p.productID = #url.productID#
> OR p.productID IS NULL)
> AND l.status = 1
> =================================
>
> Basically I have the tables above (let me populate some data:
>
> Levels
> levelID, code, description, status
> 1 555 Wholesale 1
> 2 444 Gold Member 1
> 3 222 Silver Member 0
>
> LevelPricing
> levelPriceID, levelID, productID, Price
> 1 1 18 30.00
> 2 1 29 25.00
> 3 2 18 25.00
> 4 2 42 27.00
>
> I want to pull out WHOLESALE AND GOLD MEMBER (Silver is status 0 so it is turned off).
>
> I should be pulling out 2 records IF I am passing URL.PRODUCTID = 18, I should have 30.00 and 25.00 for wholesale and gold.
>
> IF my url.productID is 0 (a new product page). Then I STILL want to pull out the two rows for WHOLESALE and GOLD MEMBER, but the price should be NULL (since there is not a record for productID 0 in the levelPricing table.
>
> SO for productID = 18
> l.levelID, l.code, l.description, p.price
> 1 555 Wholesale 30.00
> 2 444 Gold Member 25.00
>
> For ProductID = 0
> l.levelID, l.code, l.description, p.price
> 1 555 Wholesale NULL
> 2 444 Gold Member NULL
>
> On the product building page I would like to list out all the various descriptions of level pricings from the levels table. IF the productID matches up, I would like to output the associated price with the level. IF productID is 0 (or a new record), I would like it to list out the levels.description with a NULL price.
>
> This query is not working, if there are prices associated with the productID it shows the levels, but if there are not pricing associated with the productID it doesn't show the various levels.
>
> Any suggestions? I have tried multiple variations without avail. (Needs to work in MS Access and MS SQL)
>
> Paul Giesenhagen
> QuillDesign
> 417-885-1375
> http://www.quilldesign.com
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
