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?
jon100Asked:
Who is Participating?
 
simonbennettCommented:
...or you can alias, e.g.

Don't use * - explicitly call your fields, then when you have 2 the same, alais e.g.

Select Table1.Field1 as T1F1, Table2.Field1 as T2F1 From Table1, Table 2

...and refer to rs("T1F1")

HTH

Simon
0
 
weesiongCommented:
jon100,

Sample

Select table1.userid as userid1, table2.userid as userid2 from table1, table2

Rs("userid1")
Rs("userid2")

Good luck :)

Regards,
Wee Siong
0
 
nilapennCommented:
select * from table1,table2 where table1.fieldname=table2.fieldname

using the above query
rs.fields("Table1.fieldname").value does work prefectly for me
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dedsiCommented:
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).
0
 
jon100Author Commented:
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?
0
 
jon100Author Commented:
nilapenn, it does appear to work when I use the following syntax:

Rs.Fields("tblProducts.fldPoints").value

Ill check it and get back 2u
0
 
jon100Author Commented:
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?!
0
 
jon100Author Commented:
nilapenn - can you give me a longer listing please.

What kind of recordset are you building etc?
0
 
weesiongCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.