I just test this code in MSSQL. Access my not like the or, but this
one works for ProductID of 0 or 18 and works the way you want. I am not
sure if access will like it. You don't have to stipulate the ProductID
as null since you are doing an outer join, also, sorry I didn't catch
this last time, but you want a left outer join, not right.
SELECT l.LevelID, l.Code, l.description, p.Price
FROM Levels l LEFT OUTER JOIN
LevelPricing p ON l.LevelID = p.LevelID AND
p.ProductID = #url.ProductID#
WHERE (l.Status = 1)
Christian
-----Original Message-----
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 5:13 PM
To: CF-Talk
Subject: Re: SQL Query
I tried your example query, and recieved a "join _expression_ not
supported" error. I assume that is an MS Access error ..
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)
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
