jon100
asked on
Using INNER JOIN, referencing fields with same name in diff tables
I have the following SQL Statement
SELECT * FROM tblClaims INNER JOIN tblUsers ON tblClaims.fldUserID = tblUsers.fldUserID INNER JOIN tblProducts ON tblClaims.fldProductID = tblProducts.fldProductID WHERE tblClaims.fldVerified = 0
It works fine, I put it into a recordset. How do I then access fields from the different tables?
Rs("Points") - for example. What if there is a UserID in two of the tables that comes up with different values on any 1 recordset?
Ive tried Rs("tblblah.points") - but it doesn't work. How can I do this?
SELECT * FROM tblClaims INNER JOIN tblUsers ON tblClaims.fldUserID = tblUsers.fldUserID INNER JOIN tblProducts ON tblClaims.fldProductID = tblProducts.fldProductID WHERE tblClaims.fldVerified = 0
It works fine, I put it into a recordset. How do I then access fields from the different tables?
Rs("Points") - for example. What if there is a UserID in two of the tables that comes up with different values on any 1 recordset?
Ive tried Rs("tblblah.points") - but it doesn't work. How can I do this?
select * from table1,table2 where table1.fieldname=table2.fi eldname
using the above query
rs.fields("Table1.fieldnam e").value does work prefectly for me
using the above query
rs.fields("Table1.fieldnam
The fieldnames must be unique, if they are just use Rs("fieldname"). If there not you can only get one of the fields, which one it will be depends on your sql-server (though i think the order in which you write the tables make a diffrence).
ASKER
ahh
if i run a query in sql server it shows me all of the columns (including the ones with the same field names) - I take it there is no way to get them all in 1 recordset on an asp page unless all field names are unique then?
if i run a query in sql server it shows me all of the columns (including the ones with the same field names) - I take it there is no way to get them all in 1 recordset on an asp page unless all field names are unique then?
ASKER
nilapenn, it does appear to work when I use the following syntax:
Rs.Fields("tblProducts.fld Points").v alue
Ill check it and get back 2u
Rs.Fields("tblProducts.fld
Ill check it and get back 2u
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nilapenn - for some reason I cant get it working now.
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
Thanx simon - if I cant get it working the way I want i'll use your technique.
It would make sense that you should be able to do it the way I tried it though?!
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
Thanx simon - if I cant get it working the way I want i'll use your technique.
It would make sense that you should be able to do it the way I tried it though?!
ASKER
nilapenn - can you give me a longer listing please.
What kind of recordset are you building etc?
What kind of recordset are you building etc?
jon100,
This one:
Select table1.userid as userid1, table2.userid as userid2 from table1, table2
Rs("userid1")
Rs("userid2")
And this one:
Select Table1.Field1 as T1F1, Table2.Field1 as T2F1 From Table1, Table 2
...and refer to rs("T1F1")
Got any different?
Regards,
WEe Siong
This one:
Select table1.userid as userid1, table2.userid as userid2 from table1, table2
Rs("userid1")
Rs("userid2")
And this one:
Select Table1.Field1 as T1F1, Table2.Field1 as T2F1 From Table1, Table 2
...and refer to rs("T1F1")
Got any different?
Regards,
WEe Siong
Sample
Select table1.userid as userid1, table2.userid as userid2 from table1, table2
Rs("userid1")
Rs("userid2")
Good luck :)
Regards,
Wee Siong