How to write this elegantly in SQL ?

Question

I have a table (user) that contains user information.

I have another table (userview) that records user views - that is, when one user views another users details, I record the id of the viewer and also, the id of the user he/she viewed.

I want to write a query that will help me answer the question:

Fetch the list of ALL users that were viewed by users that viewed a SPECIFIED user.

It seems a straight forward enough innocous requirement, but I am coming up with SQL that is (atleast to my mind), far too complicated. I think an elegant solution would use a self join on userview and (possibly?) an inner join between user and userview.

Are there any SQL 'poets' out there that can help with this?

I am using mySQL 5.x, but ideally the SQL should be fb agnostic

Answer 1

SELECT * 
    FROM userviews 
    WHERE viewer IN (
                   SELECT viewer 
                       FROM userviews 
                       WHERE viewed = 123)

The subquery gets the ids of the viewers that looked at the specified user (in this case 123), the entire query simply gets all rows for this viewers.

Answer 2

Lets start with "users that viewed this user":

select v1.viewer
from userview v1
where v1.viewee = @thisUserId

Then add "users that were viewed by":

select v2.viewee
from userview v1
inner join userview v2 on v2.viewer = v1.viewer
where v1.viewee = @thisUserId

Then add the user table to get the data from it:

select u.userid, u.someotherfields
from userview v1
inner join userview v2 on v2.viewer = v1.viewer
inner join user u on u.userid = v2.viewee
where v1.viewee = @thisUserId