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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
simonbennettConnect With a Mentor Commented:
...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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.