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

 

[SkipVought]

The problem is in the Select *.

Select the INDIVIDUAL fields using the table prefix

SELECT [JOBS].[UniqueJobCode],....  
FROM ([JOBS] J INNER JOIN [EXTRA] E ON 
[JOBS].[UniqueJobCode] = [EXTRA].[UniqueJobCode]) 
INNER JOIN [JOBSETTINGS] ON 
[JOBS].[UniqueJobCode] = [JOBSETTINGS].[UniqueJobCode]

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[AndyMcDonald]

Thanks Skip,

Unfortunately, I use common code for several different databases and the users can add any fields that they want to the databases so generating a select that lists all of the fields will be a pain (though I'll do it if I have to!). What I'm really trying to get to the bottom of is why I get different column names (for the duplicated columns) when using Access as opposed to SQL Server?

 

[lespaul]

that's the way each db deals with including fields from different tables that each have the same name. The AS400 that I retrieve from adds a '_#'

What is the issue you're having with this?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[AndyMcDonald]

Hi lespaul,

My problem is that the application we've developed could be used with practically any type of database, therefore I need the column names to be returned the same way regardless of the database type that I'm connecting to.

Otherwise, I either need to get rid of the duplicated columns (to ensure that I only get one called "UniqueJobCode") or I have to generate the list of all of the columns programmatically (which would be painful becuase the users can add more columns if they wish).

 

[Golom]

Unfortunately, using * as your field specification in SELECT is the basic cause of the problem as SkipVought stated. Each DBMS has different ways of deciding how to name the duplicate fields so you won't get transparent portability as long as you use SELECT *. Explicit field naming in the SELECT is about the only way around it.

If you are retrieving this into a recordset then you can use relative field positions (e.g. rs.Fields(0).Value). That doesn't help much however since code based on an assumed field position can be pretty fragile in the sense that the field ordering can change if you modify the join sequence in the statement.

 

[AndyMcDonald]

Thanks Guys,

I was hoping that this was something stupid and/or simple that we'd done wrong but it obviously isn't. I'll have to go back to the drawing board to work out how to build the list of column names as part of the query building.