Multiple join headache

[insert_namehere]

Hello all - I have a relatively simple question about what I'm sure is a relatively simple problem, but I've been wrestling with it for hours and getting nowhere!

I have a database with around 45 tables in it. They all carry a column called "StudentID". When I do a simple single JOIN such as:

Set con = Server.CreateObject("ADODB.connection")
Set rs = Server.CreateObject("ADODB.recordset")
con.open(application("bugCon"))
strSQL = "SELECT First_Name, Last_Name, para.classno FROM StudentRecord JOIN para ON (StudentRecord.StudentID = para.StudentID) WHERE StudentRecord.StudentID = '1095'"

Set rs = con.execute(strSQL)

do while NOT rs.EOF
Name = rs("First_Name") &" "& rs("Last_Name")
Course = rs("classno") &"<br>"

rs.MoveNext
Loop
response.write(Name &"<HR>")
response.write(Course)
set con = nothing
set rs = nothing
set strSQL = nothing

it works fine.

However, when I write a multiple join such as:

Set con = Server.CreateObject("ADODB.connection")
Set rs = Server.CreateObject("ADODB.recordset")
con.open(application("bugCon"))



'"SELECT StudentRecord.First_Name, StudentRecord.Last_Name, para.classno FROM StudentRecord INNER JOIN para ON StudentRecord.StudentID = para.StudentID INNER JOIN CISMG ON CISMG.StudentID = StudentRecord.StudentID Where StudentRecord.StudentID = '1095' GROUP BY StudentRecord.First_Name, StudentRecord.Last_Name, para.classno"

strSQL = "SELECT First_Name, Last_Name, para.classno, CISMG.classno FROM StudentRecord JOIN para ON (StudentRecord.StudentID = para.StudentID) Left JOIN CISMG ON (CISMG.StudentID = StudentRecord.StudentID) WHERE StudentRecord.StudentID = '1095'"

Set rs = con.execute(strSQL)
Name = rs("First_Name") &"&nbsp;"& rs("Last_Name")
do while NOT rs.EOF

Course1 = rs("para.classno") &"<br>"

Course2 = rs("CISMG.classno") &"<br>"

rs.MoveNext
Loop
response.write(Name &"<HR>")
response.write(Course)
set con = nothing
set rs = nothing
set strSQL = nothing

I get the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/database/multijoin.asp, line 16

WHAT am I doing wrong!!

Thanks in advance, your friendly neighborhood idiot.

[chazzy]

more likely than not, an ASP error (and as a result I'll throw it there), and i'm thinking it's because of this clause in your sql:


para.classno, CISMG.classno


you should give them aliases because when you issue the sql, it only keeps the name, not the prefix + name. there won't be a "para.classno" and "CISMG.classno"

[insert_namehere]

more likely than not, an ASP error (and as a result I'll throw it there), and i'm thinking it's because of this clause in your sql:


para.classno, CISMG.classno


you should give them aliases because when you issue the sql, it only keeps the name, not the prefix + name. there won't be a "para.classno" and "CISMG.classno"

Ummm.... that sounds reasonable.... however, and bear with me please - could you give me an example of HOW to create an alias? I'm really lost on the issue.

Thanks,
Tom

[insert_namehere]

Okay, I may have SORT of figured out aliases, and rewrote it so:

Set con = Server.CreateObject("ADODB.connection")
Set rs = Server.CreateObject("ADODB.recordset")
con.open(application("bugCon"))



strSQL = "SELECT A1.First_Name, A1.Last_Name, A2.classno, A3.classno FROM StudentRecord A1 JOIN para A2 ON (A1.StudentID = A2.StudentID) JOIN CISMG A3 ON (A3.StudentID = A1.StudentID) WHERE A1.StudentID = '1095'"

Set rs = con.execute(strSQL)
Name = rs("First_Name") &"&nbsp;"& rs("Last_Name")
do while NOT rs.EOF

Course = rs("classno") &"<br>"

rs.MoveNext
Loop
response.write(Name &"<HR>")
response.write(Course)
set con = nothing
set rs = nothing
set strSQL = nothing


But now I get THIS error message:


error '80020009'
Exception occurred.

/database/multijoin.asp, line 11



What did I mess up now?
thanks in advance - Tom

[chazzy]

in a query, you alias a column like this:


select
column_a as colA,
column_b as colB,
....

so then you reference them "colA" and "colB" . the code you're showing is table aliases, but in this case you need to use a column alias.