Link to home
Start Free TrialLog in
Avatar of jon100
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?
Avatar of weesiong
weesiong

jon100,

Sample

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

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

Good luck :)

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

using the above query
rs.fields("Table1.fieldname").value does work prefectly for me
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).
Avatar of jon100

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?
Avatar of jon100

ASKER

nilapenn, it does appear to work when I use the following syntax:

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

Ill check it and get back 2u
ASKER CERTIFIED SOLUTION
Avatar of simonbennett
simonbennett

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jon100

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?!
Avatar of jon100

ASKER

nilapenn - can you give me a longer listing please.

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