INNER JOIN Query/Recordset Use

[JohnBoy2005]

I'm using an INNER JOIN Query to populate a recordset.

"SELECT tblMessages.*, tblMessagesLink.* FROM tblMessages INNER JOIN tblMessagesLink ON tblMessages.MID = tblMessagesLink.MID WHERE tblMessages.MID = '" & strMsgID & "' AND tblMessagesLink.User_ID = " & intUID"

User_ID is common in both tables, how do I reference them

In VB6 I would use rstTemp("tblMessages.User_ID") or rstTemp("tblMessagesLink.User_ID") and get the correct data.

I try this method in ASP and I get errors.

Cheers

John

 

[BigRed1212]

There is probably a better way, but if I were doing it I would do this:

1. Make your SQL
"SELECT tblMessages.User_ID as Apple, tblMessages all the other fields you want, tblMessagesLink.User_ID as Grape, tblMessagesLink all the other fields that you want FROM tblMessages INNER JOIN tblMessagesLink ON tblMessages.MID = tblMessagesLink.MID WHERE tblMessages.MID = '" & strMsgID & "' AND tblMessagesLink.User_ID = " & intUID"

And then write

<%= rstTemp("Apple")%>
<%= rstTemp("Grape")%>

Of course you should choose better names.

 

[JohnBoy2005]

Thanks for that.

Just discovered the "AS" function about an hour ago.

Weird how the two systems differ.

Cheers

john