Request Query with three tables

[fran7]

Hi, I wonder if there are any pointers to the following problem.

I Have three tables.

tbllogin
L.userid

tblprofile
P.userid,p.profileid,name,surname,etc

tblWatches
WatchingUserID,watchedprofileid

In each table these ids are the same
L.userid,P.userid,WatchingUserID

In the query below I collect all the watchedprofileid, being followed by WatchingUserID
watchedprofileid = p.profileid

lngprofileid=CLng(Request("UserID"))
If lngprofileid <> "" And lngprofileid <> 0 Then
 
Set rs=Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
SQLQuery="Select L.accept, etc,etc,etc FROM tbllogin L INNER JOIN tblprofile P ON L.UserId = P.UserId "
 
SQLQuery= SQLQuery & " where 
SQLQuery= SQLQuery & " p.ProfileID in (Select WatchedProfileID from tblWatches where WatchingUserID = 0"  & Clng(lngprofileid) & ")"
 
SQLQuery= SQLQuery & " order by L.latestvisit desc"
 

It all works fine. The problem I have is I want to include in the title and description of the page, details about the userid from tblprofile such as their name, surname etc.

Unfortunately as the query is written no combination and playing around with the query will get that info.

I just wondered if there was a different way to achieve the same result plus info from tblprofile about the userid that is used in the query.
Any pointers would be a great help
Thanks in advance
Richard

[Rabbit]

I don't understand what you mean. You're joining to the tblprofile so you shouldn't have any issues getting fields from that table.

[fran7]

Hi,
I have a user (no 1), he follows other users and I collect those he follows on a page. In the page all the right info is displayed, as you rightly said, from tblprofile that I want.

However, I want the title and description of the page to relate to the user (no 1). If I put " & Clng(lngprofileid) & " in the title I get (no 1)s userid, but if I include any more info about (no 1) from tblprofile it includes info from the first user displayed in the page and not the user who is in the querystring. Ie the user who is following all the users displayed in the page.

Sorry if I did not explain it well before. Thanks for any advice.
Richard

[Rabbit]

I think I see what you mean now. In that case, you should just run a separate query to pull that data. Trying to incorporate that into one query just repeats a bunch of unnecessary data for every row.

[fran7]

Thanks, that's what I thought but needed an expert to confirm.
Thanks again
Richard