Problem with Duplicate column name handling with Inner Join

[AndyMcDonald]

Hi,
I'm working on a problem where we use the same SQL SELECT statement to create a normalised view of three tables (called "JOBS", "EXTRA" and "JOBSETTINGS"). All three tables use "UniqueJobCode" as their key.

The SQL statement we are using is:
SELECT * FROM ([JOBS] INNER JOIN [EXTRA] ON
[JOBS].[UniqueJobCode] = [EXTRA].[UniqueJobCode])
INNER JOIN [JOBSETTINGS] ON
[JOBS].[UniqueJobCode] = [JOBSETTINGS].[UniqueJobCode]

This query works fine, except:-

When I run the query connected to an Access database, the normalised table contains three entries called [JOBS.UniqueJobCode], [EXTRA.UniqueJobCode] and [JOBSETTINGS].UniqueJobCode whereas if I run the query connected to an SQL Server based database (of exactly the same structure) I get three entries called [UniqueJobCode], [UniqueJobCode1] and [UniqueJobCode2].

I would "code round" this problem except that I would rather get to the bottom of it to ensure that it isn't causing me problems elsewhere.

Thanks,

Andy

 

[jbenson001]

You are telling the sql to pull back all columns from all tables.. because of the () around your first inner join.

Remove them, and only columns from JOBS will show based in the inner joins.

 

[AndyMcDonald]

Thanks,
But my problem is that I need all of the data to be present (this is a "normalised" view of three related tables) but I need to understand why the names given to the UniqueJobCode columns changes when I connect to SQL Server versus MS Access databases

 

[jbenson001]

You have 3 columns with the same name. In order to differenciate between them, SQL prefixes the column name with the table name(tablename.columnname) so that you know what data is from what table.

 

[AndyMcDonald]

I can understand that part, but what I don't understand is why I get different column names if I use Access instead of SQL server?

 

[jbenson001]

not sure what you mean by this. Show results from both so i can understand what you are trying to tell me.

 

[jbenson001]

I see what you are saying. I have tried to replicate your problem but cannot. Are you running this in QA or a stored procedure?

 

[rac2]

Possibly the issue is

SELECT *

This shorthand must be elaborated to unique column names. While we know that it means to show all of the columns from all of the tables in the joins, there is the problem of how to distinquish columns with the same name.

It appears that Access does this by adding the table name; and SQL Server does it by constructing new column names.

Possibly there is no ANSI standard way to implement this elaboration.

You might achieve consistency in the two systems with this-

SELECT JOBS.*, EXTRA.*, JOBSETTINGS.*