Join three tables with sum

[fran7]

Hi, I wonder if anyone can help.

I have two tables joined.

L.userid, p.profileid
from tblLoginDatabase L inner JOIN tblProfilesDatabase P ON L.UserId = P.UserId"

I need to join to this another table with two fields


tablewatches

WatchingUserID WatchedProfileI D

WatchingUserID = P.UserId

WatchedProfileI D = P.ProfileId

I need to then display the records where sum of WatchedProfileI D is the highest.


I imagine i need to add

SUM(tablewatches.WatchedProfileID) AS totalhits from but get error 500

but not sure how that fits with any join.

Any pointers would be great.
Thanks
Richard

[r035198x]

Does "where sum of WatchedProfileI D is the highest." mean only one record?
Maybe post some example records and expected output.

[fran7]

Hi, Thanks for the reply. I might actually have meant count.

WatchingUserID is the member following another member WatchedProfileI D

So the user who is being watched the most, ie count the number of times they are being watched and then display those who are being watched the most first.


WatchingUserID WatchedProfileI D
345 4536
345 5335
3432 5335
664 3434
4433 5335
4334 3434

member 5335 would come top.

users sign up and get a userid from
tblLoginDatabas e = userid

they then create a profile and have also a profileid
tblProfilesData base = userid and profileid

so
WatchingUserID = tblProfilesData base.userid
and
WatchedProfileI D = tblProfilesData base.profileid


Hope that might help.
Thanks
Richard

[r035198x]

start with

select WatchingUserID , count ( WatchedProfileID) from tablewatches group by WatchingUserID order by count ( WatchedProfileID) desc

[VanessaMeacham]

HI ! I totally agreed with r035198x.

[fran7]

Thanks for the reply. I wonder if this is a separate "select" that I need to add after the main query or if I have to integrate it with it. I have tried both and not sure if I was doing it right, both failed.

L.userid, p.profileid
from tblLoginDatabase L inner JOIN tblProfilesDatabase P ON L.UserId = P.UserId"

Thanks for any pointers.
Richard

[r035198x]

You can get associated values from other tables by joining with something like

untested:

select u.*, topWatchers.total from users u inner join
(select WatchingUserID , count ( WatchedProfileID) total from tablewatches group by WatchingUserID order by count ( WatchedProfileID) ) topWatchers

on (u.userId = topWatchers.WatchingUserID )

[fran7]

Hi, Just to say thanks for pointing me in the right direction.
I got it working with this

FROM LoginDatabase L inner JOIN ProfilesDatabase P ON L.UserId = P.UserId left outer join(select WatchedProfileID, count(WatchedProfileID) results from tblWatches  group by WatchedProfileID) S on P.profileId = S.WatchedProfileId order by results desc

Thanks
Richard