Re: Cannot port OUTER join code from SQL Server to Access 2000

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Brian (bcap_at_IHATESPAMclara.co.uk)
Date: 02/20/05 

Date: Sun, 20 Feb 2005 09:27:57 -0000

"ChrisG (UK)" <news@nospam.thegrahams.freeserve.co.uk> wrote in message
news:cv5k4q$vnb$1@news6.svr.pol.co.uk...
>
> My first post so be gentle with me :o)
>
> 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

Relevant Pages

  • Re: Legality of drawing interest on funds put into personal account from business account
    ... checking account until the credit card bill comes around. ... The most important reason for doing business as a corporation rather than as ... by the corporation and the owner will not be personally liable. ...
    (misc.legal)
  • Re: Adverse possession - daftest question I suppose
    ... could I end up landed with the bill for inheritance tax and the ... The rules for getting registered as the owner of a property like this ... registered then they can apply after 10 years but the Land Registry ... the claim must arise from a situation of -adverse- possession. ...
    (uk.legal)
  • Re: How to pay contractor, who to make check out to?
    ... the owner. ... In theory, if I hadn't paid "the company", ... I believe there could have been 'creditors' come after me for a 'company' ... bill not paid. ...
    (alt.home.repair)
  • Re: British Telecom Tossers
    ... The new owner took over the line and that was that. ... debt collection agencies were chasing me for the final BT bill. ... I obviously paid the bill but refused to pay the late payment fee as the late payment was their fault for not sending the bill to my new address. ...
    (uk.finance)
  • Cannot port OUTER join code from SQL Server to Access 2000
    ... I have an owner table ... Sid owns a type 1 ... Bill owns a type 2 ... INNER JOIN owned ON owner.id = owned.owner_id ...
    (microsoft.public.access.queries)