SQL INNER JOIN Field Namimg Conventions

#1 11-05-2005, 10:00 AM

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!

#2 11-05-2005, 10:25 AM

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"

#3 11-05-2005, 11:41 AM

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)

#4 11-05-2005, 11:46 AM

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"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode