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]
