Bug in Outer Join Query -- Access 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Bug in Outer Join Query -- Access 2000

Author

Topic

apollo11
Starting Member

6 Posts

Posted - 2001-11-28 : 20:11:38

The following query works fine in SQL Server 7, but gives an error in Access 2000 (using the Northwind DB):

======================================================================
SELECT O.OrderID, O.CustomerID, O.OrderDate, O.ShipName, D.UnitPrice, D.Discount
FROM Orders AS O
LEFT OUTER JOIN [Order Details] AS D
ON (O.OrderID = D.OrderID) AND (D.Discount = 0)
======================================================================

When I try to save the above query (written in SQL view), I get the following error:
"Join expression not supported"

The problem is the 2nd "ON" criteria: "AND (D.Discount = 0)"

According to the MSDN site, Access 2000 is SQL92 compliant. Is so, why doesn't this query work?

TIA.

Best Regards,
Jim Underwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-28 : 20:24:01

Try this:

SELECT O.OrderID, O.CustomerID, O.OrderDate, O.ShipName, D.UnitPrice, D.Discount
FROM Orders AS O
LEFT JOIN [Order Details] AS D ON (O.OrderID = D.OrderID)
WHERE D.Discount = 0

Access SQL is not entirely SQL-92 compliant; it simply supports the minimum compliance features. Also, the syntax for joins is finicky. If in doubt, design your queries using the Access query designer, then examine the SQL it generates in the SQL view.

apollo11
Starting Member

6 Posts

Posted - 2001-11-28 : 21:48:21

Thanks for the reply.

Actually I've have tried your suggestion. It's not the same. The WHERE clause eliminates all the NULL [Order Detail] records I'm trying to include by using an outer join.

I'm moving from Access to MSDE/SS7 anyway. The sooner the better.

Thanks,
jmu

quote:

Try this:

SELECT O.OrderID, O.CustomerID, O.OrderDate, O.ShipName, D.UnitPrice, D.Discount
FROM Orders AS O
LEFT JOIN [Order Details] AS D ON (O.OrderID = D.OrderID)
WHERE D.Discount = 0

Best Regards,
Jim Underwood

mono
Starting Member

36 Posts

Posted - 2001-11-29 : 08:13:19

I think Access is choking on "OUTER". A LEFT or RIGHT implies OUTER anyway.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-29 : 12:24:05

quote:
Actually I've have tried your suggestion. It's not the same. The WHERE clause eliminates all the NULL [Order Detail] records I'm trying to include by using an outer join.

I'm in favor of you moving to SQL 7 or 2K, but about your comment above. What exactly, did you expect your JOIN ... AND D.Discount = 0 to do if not limit your results to the rows where D.Discount = 0 (thus eliminating rows where D.Discount is NULL)?

-------------------
It's a SQL thing...

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-29 : 13:37:36

Mark, try this:


CREATE TABLE T1 (
	k1 int NOT NULL ,
	d1 char(5) NOT NULL
)


INSERT INTO T1 VALUES (1, 'one')
INSERT INTO T1 VALUES (2, 'two')
INSERT INTO T1 VALUES (3, 'three')


CREATE TABLE T2 (
	k2 int NOT NULL ,
	d2 char(5) NOT NULL
)


INSERT INTO T2 VALUES (1, 'test')
INSERT INTO T2 VALUES (2, 'test')
INSERT INTO T2 VALUES (4, 'test')


SELECT *
FROM T1
LEFT OUTER JOIN T2 ON k1 = k2 AND d2 = 'test'


SELECT *
FROM T1
LEFT OUTER JOIN T2 ON k1 = k2
WHERE d2 = 'test'

Oops: forgot to include the result


k1          d1    k2          d2    
----------- ----- ----------- ----- 
1           one   1           test 
2           two   2           test 
3           three NULL        NULL


(3 row(s) affected)


k1          d1    k2          d2    
----------- ----- ----------- ----- 
1           one   1           test 
2           two   2           test 


(2 row(s) affected)

Edited by - Arnold Fribble on 11/29/2001 14:41:21

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-29 : 15:00:21

Oh yeah, my bad. Thanks for the example code and results. Makes it really clear.

As far as the Access code goes, I haven't tried this, but I know that Access can be really finicky when it comes to parentheses. You might try something like

LEFT OUTER JOIN [Order Details] AS D
ON ((O.OrderID = D.OrderID) AND (D.Discount = 0))

or some other mixture...

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources