can I prefix columns in a join in jet (access)?

Question

I've got a demo app, using php with an Access/Jet backend. I've got these (simplified) tables:

People
------
ID
BankID    // foreign key in Banks
Name
Address

Banks
--------
ID
Name
Address

I'm doing a join:

SELECT * FROM People LEFT JOIN on People.BankID = Banks.ID

What's the best way to avoid name clashes? This works:

SELECT People.*, 
Banks.ID as BankID, 
Banks.Name as BankName, 
Banks.Address as BankAddress 
FROM People LEFT JOIN on People.BankID = Banks.ID

but it gets awful wordy when you have more than a few columns. Is there any way to say "prefix all of the columns in the Banks table with 'Bank'"?

Answer 1

No, although you could use an Alias to shorten the table name.

Well, it's likely too late now but I use a field naming convention that avoids duplicate field names completely. Tony's Table and Field Naming Conventions Some people like it. Some people have independently come up with the same solutions. Others hate it. shrug

That said if you use the query builder Access puts in all the table names for you when you look at the SQL statement it produces.

Answer 2

There's no "prefix my columns" feature that you can turn on, but since you're already working in PHP it should be pretty straightforward to generate SQL with the appropriate " AS " keywords. Just loop through the fields and tables you want, with some string comparisons.

As Tony mentioned, Access will do this automatically if you use query builder, so if your queries are predefined you can let Access do this for you.

Answer 3

Use table correlation names (colloquially 'aliases') to qualify the columns in your SQL e.g. here P1 and B1 are correlation names:

SELECT P1.*,
       B1.ID AS BankID,
       B1.Name AS BankName,
       B1.Address AS BankAddress
  FROM People AS P1
       LEFT OUTER JOIN Banks AS B1
          ON P1.BankID = B1.ID;