Cannot port OUTER join code from SQL Server to Access 2000

[ChrisG \(UK\)]

My first post so be gentle with me )

I have an owner table
Id Name
1 Sid
2 Bill
3 Fred

This links through Owner_id to the Owned table
Id Owner_id type
1 1 1
2 2 2
3 3 1
4 3 2

Sid owns a type 1
Bill owns a type 2
Fred owns a type 1 and 2

I want to search for type 1 owners and have them returned plus an additional
column that shows if they also own a 2
Id Name type2
1 Sid NULL
3 Fred 2

In SQL Server following code works
SELECT owner.id, owner.name ,o2.type
FROM owner
INNER JOIN owned ON owner.id = owned.owner_id
left outer join owned o2 on owner.id=o2.owner_id and o2.type=2
Where owned.type=1

Access initially gives me this
SELECT owner.id, owner.name
FROM owner
INNER JOIN owned ON owner.id = owned.owner_id
WHERE (((owned.type)=1));

Then I add
left outer join on owned as o2 where o2.owner_id = owner.id and o2.type=2
And it complains.

Any help gratefully received.

Chris....

 

[Brian]

My first post so be gentle with me )

I have an owner table
Id Name
1 Sid
2 Bill
3 Fred

This links through Owner_id to the Owned table
Id Owner_id type
1 1 1
2 2 2
3 3 1
4 3 2

Sid owns a type 1
Bill owns a type 2
Fred owns a type 1 and 2

I want to search for type 1 owners and have them returned plus an additional
column that shows if they also own a 2
Id Name type2
1 Sid NULL
3 Fred 2

In SQL Server following code works
SELECT owner.id, owner.name ,o2.type
FROM owner
INNER JOIN owned ON owner.id = owned.owner_id
left outer join owned o2 on owner.id=o2.owner_id and o2.type=2
Where owned.type=1

Access initially gives me this
SELECT owner.id, owner.name
FROM owner
INNER JOIN owned ON owner.id = owned.owner_id
WHERE (((owned.type)=1));

Then I add
left outer join on owned as o2 where o2.owner_id = owner.id and o2.type=2
And it complains.

Any help gratefully received.

Chris....

Unfortunately Access is much less smart than SQL Server at figuring out join
expressions. Access insists on having parentheses to help it understand.
This will probably do it for you:

SELECT owner.id, owner.name ,o2.type
FROM (owner
INNER JOIN owned ON owner.id = owned.owner_id)
left join owned o2 on owner.id=o2.owner_id and o2.type=2
Where owned.type=1

 

[ChrisG (UK)]

Unfortunately Access is much less smart than SQL Server at figuring out join
expressions. Access insists on having parentheses to help it understand.
This will probably do it for you:

SELECT owner.id, owner.name ,o2.type
FROM (owner
INNER JOIN owned ON owner.id = owned.owner_id)
left join owned o2 on owner.id=o2.owner_id and o2.type=2
Where owned.type=1

Access complains with a "Join expression not supported" error and
highlights the o2.type=2

(

 

[Michel Walsh]

Hi,


Either use a virtual table

.... LEFT JOIN (SELECT ... WHERE type=2) As o2 ON owner.id=o2.owner.id


either move it in the where clause

...
LEFT JOIN owned AS o2 on owner.id=o2.owner.id
WHERE owned.type=1
AND (o2.type=2 OR o2.type Is NULL)



Hoping it may help,
Vanderghast, Access MVP

 

[Brian]

Access complains with a "Join expression not supported" error and
highlights the o2.type=2

(

OK, I didn't notice the multiple criteria in the join expression. All you
need is a couple more brackets, thus:

SELECT owner.id, owner.name ,o2.type
FROM (owner
INNER JOIN owned ON owner.id = owned.owner_id)
left join owned o2 on (owner.id=o2.owner_id and o2.type=2)
Where owned.type=1