SQL INNER JOIN Field Namimg Conventions

Ok...been working with ASP for a while and something that has always
stumped me:

My SQL statement is like so:

SELECT * FROM tutorSessions INNER JOIN tutors On
tutorSessions.provider_id = tutors.provider_id INNER JOIN students ON
tutorSessions.student_id = students.id

Now, in my tutors table and my students table, I have 2 fields each
with the same name, lastName and firstName.

My problem is how to I reference each field in their respective tables?
I've tried several things like:

sql = "SELECT * FROM tutorSessions INNER JOIN tutors On
tutorSessions.provider_id = tutors.provider_id INNER JOIN students ON
tutorSessions.student_id = students.id"
Set RS3 = Conn.Execute(sql)
response.write(RS3.Fields("students.lastName"))

Error

And the such. Thanks for the help!

Re: SQL INNER JOIN Field Namimg Conventions

ryan@jpmicro.com wrote:
> Ok...been working with ASP for a while and something that has always
> stumped me:
>
> My SQL statement is like so:
>
> SELECT * FROM tutorSessions INNER JOIN tutors On
> tutorSessions.provider_id = tutors.provider_id INNER JOIN students ON
> tutorSessions.student_id = students.id
>
> Now, in my tutors table and my students table, I have 2 fields each
> with the same name, lastName and firstName.
>
> My problem is how to I reference each field in their respective
> tables? I've tried several things like:
>
> sql = "SELECT * FROM tutorSessions INNER JOIN tutors On

Stop using selstar.
http://www.aspfaq.com/show.asp?id=2096

> tutorSessions.provider_id = tutors.provider_id INNER JOIN students ON
> tutorSessions.student_id = students.id"
> Set RS3 = Conn.Execute(sql)
> response.write(RS3.Fields("students.lastName"))
>
The ONLY way to distinguish between them is to stop using selstar and use
column aliases to give them different names.

SELECT t.lastName as TutorLastName, t.firstName as TutorFirstName,
s.lastName as StudentLastName, s.firstName as StudentFirstName
FROM tutorSessions t INNER JOIN students s
On t.student_id = s.id

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL INNER JOIN Field Namimg Conventions

Bob Barrows [MVP] wrote on 05 nov 2005 in
microsoft.public.inetserver.asp.db:

> SELECT t.lastName as TutorLastName, t.firstName as TutorFirstName,
> s.lastName as StudentLastName, s.firstName as StudentFirstName
> FROM tutorSessions t INNER JOIN students s
> On t.student_id = s.id
>

Can we do without the AS?

FROM tutorSessions t INNER JOIN students s

FROM tutorSessions AS t INNER JOIN students AS s

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: SQL INNER JOIN Field Namimg Conventions

Evertjan. wrote:
> Bob Barrows [MVP] wrote on 05 nov 2005 in
> microsoft.public.inetserver.asp.db:
>
>> SELECT t.lastName as TutorLastName, t.firstName as TutorFirstName,
>> s.lastName as StudentLastName, s.firstName as StudentFirstName
>> FROM tutorSessions t INNER JOIN students s
>> On t.student_id = s.id
>>
>
> Can we do without the AS?
>
> FROM tutorSessions t INNER JOIN students s
>
> FROM tutorSessions AS t INNER JOIN students AS s

Usually, the "AS" is optional. I have run into a few cases (especially in
Access) where it was required for some reason.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"