I am trying to make this work with both MS SQL Server and MS Access (I can use two queries if necessary). Currently I am working in Access for this application.
Paul Giesenhagen
QuillDesign
417-885-1375
http://www.quilldesign.com
----- Original Message -----
From: Christian Watt
To: CF-Talk
Sent: Friday, August 13, 2004 5:02 PM
Subject: RE: SQL Query
You need to take your Where clause out and include it on the join. I
believe that since you are including the outer joined table in the
where, you are not going to return any records that don't match the
Where statement.
FROM #request.levelTable# l
RIGHT OUTER JOIN #request.levelPricingTable# p ON l.levelID = p.levelID
and (p.productID = #url.productID# OR p.productID IS NULL)
-----Original Message-----
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 4:50 PM
To: CF-Talk
Subject: OT: SQL Query
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]
