How do you get MySQL to return the table names in the resultset when more than one table is in the select statement using ADO ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noah Bawdy - 31 Dec 2004 00:45 GMT

This should be a <duh> but I can't find the answer.
For example:

SELECT Company_Defaults.CompanyID, Company.CompanyID
FROM Company_Defaults Left Join Company on Company_Defaults.CompanyID =
Company.CompanyID
Where Company.CompanyID = 1
And Company_Defaults.CompanyID = 1

This is over-simplified but it will give you the symptom.
With Access, Microsoft SQL Server, Sybase and Oracle, the resultset will
have the fields as Company.CompanyID and Company_Defaults.CompanyID.
With MySQL both fields are returned as just CompanyID. How do I get MySQL to
include the table names for identically named fields ?

TIA

Noah

Reply to this Message

Douglas J. Steele - 31 Dec 2004 01:20 GMT

Alias the fields:

SELECT Company_Defaults.CompanyID AS DefaultCompanyID, Company.CompanyID
FROM Company_Defaults Left Join Company on Company_Defaults.CompanyID =
Company.CompanyID
Where Company.CompanyID = 1

(FWIW, there's no reason to specify both fields in the WHERE clause since
you've already specified that they must be equal in the ON clause)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> This should be a <duh> but I can't find the answer.
> For example:
[quoted text clipped - 14 lines]
>
> Noah

Reply to this Message

Noah Bawdy - 31 Dec 2004 01:52 GMT

Thanks for the reply Doug. I was hoping to avoid the aliasing because I'm
converting a huge program that has hundreds of lines of code where the
select statement usually has Select Company.*, Company_Defaults.* (There's
actually scores of tables involved in hundreds of similar statements). I
realize the Where clause is redundant given the Join, I inherited the code
from a guy that had no prior experience in SQL and haven't been able to
clean it all up yet. Any ideas other than aliasing ?

Thanks again.

Noah

> Alias the fields:
>
[quoted text clipped - 25 lines]
> >
> > Noah

Reply to this Message

Douglas J. Steele - 31 Dec 2004 12:45 GMT

Sorry, nothing comes to mind.

Of course, that's yet another reason not to use * in queries!

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for the reply Doug. I was hoping to avoid the aliasing because I'm
> converting a huge program that has hundreds of lines of code where the
[quoted text clipped - 38 lines]
> > >
> > > Noah

Reply to this Message

VB Forum / Database Access / December 2004

How do you get MySQL to return the table names in the resultset when more than one table is in the select statement using ADO ?