join not supported

[LuigiX]

Hi

The following query works okay in SQL Query Analyzer but when I try to paste it into the SQL view of an Access query I get a "Join not supported" error. I dont think it like the left outer join. Actually I have aseries of left outers in the full query but I've abridged it.

SELECT
a.Local_Id as Agreement_No
, a.Vicinity as Street_No
, b.Descript as Road
, c.Descript as Locality
, d.Detail_text as Owners_Name

from ((asset a
inner join area_location b
on a.Location = b.Area_Code)
inner join locality c
on a.Locality = c.Locality)
left outer join asset_details d
on a.asset_id = d.asset_id and d.detail_code = 'OWNN'

where a.Local_Id = '01472'

Thanks for your help

Cheers

Luigi

 

[RV]

Use LEFT JOIN instead of left outer join

RV

 

[AncientOne]

can you explain the difference?

 

[Pat Hartman]

When you have a syntax error you should probably post the whole query. I'm going to guess that you have an inner join to the right of one of your left joins. If that is the case, you'll need to break the query into two pieces and join the pieces (unless you can redo the joins without changing the logic).

Left Join and Left Outer Join are equivalent. Jet examples generally omit the word "outer".

 

[LuigiX]

join not supported - no progress

Hi again

I've tried the suggestions without success and made up a small database (this is attached) to illustrate the problem.

The query works well in SQL query analyser but when I paste it into a new Access query in the SQL window I get the "join not supported" error.

The query is as follows:
SELECT
a.Asset_ID
, a.Description
, b.Road_Name
, c.Locality_Name
, d.Detail_Text
, e.Detail_Text

from ((((asset a

left outer join asset_details d
on a.Asset_ID = d.Asset_ID and d.Detail_Code = 'OWNN')
left outer join asset_details e
on a.Asset_ID = e.Asset_ID and e.Detail_Code = 'OWNC')
inner join Road b
on a.Road = b.Road_Name)
inner join locality c
on a.Locality = c.Locality_Name)

where a.Asset_ID = 1238;

Problem is the query has to return a null value from teh asset_details table where an OWNN or OWNC does not exist for a given asset.


Muchos gracios

 

[AncientOne]

left joining assets to asset details won't return rows that are non-existent in the asset details table. It will return rows that exist in the asset table that aren't in the details table.

What are you trying to do? Do you want to show two entries for each asset ID, one showing OWNC , the other showing OWNN with a null entry in the Detail code column for any asset ID that is lacking one of these identifiers?

 

[LuigiX]

Anciento

You are correct.

I want the query to return both values for each asset, whether they are null or not (in which case a record will exist in the asset_details table).

Thanks for your help.

Cheers

Luigi

 

[AncientOne]

Sorry to be dense, but if an asset ID lacks one of the detail codes in the asset details table, then it also lacks the other data. ie, there is no row for it in the table,ergo, it can't appear in a select query however you join the tables.

If you want to show null rows under these circumstances, I think only a union query would provide it .

 

[LuigiX]

Anciento

The query works well in SQL query analyser ie 2 rows for each asset, null or not - which is why I'm so puzzled.

Actually I want the query to provide the data source to a report where text boxes exist for OWNC and OWNN, so that is why I've specified them in the 'select'.

Access seems to trip over the 'and' in the left join for some reason.

If you could give me a union query in my dB that would do the trick, this would be much appreciated. I'm new to this type of query. I've also been told a subquery would do it.

Cheers

Luigi

 

[AncientOne]

I don't know what this SQL analyzer you are using is, but it must be using a different flavour of SQL to Acess's.

Sorry, Luigi, but I can't see any way to make a straightforward query of any sort display data from non-existent rows with Access SQL.

There are work-rounds, but they would involve adding rows to your asset details table where a detail code is missing.


I will now wait for Pat Hartman to come up with a simple and blindingly obvious answer to your problem....