SQLTeam.com | Weblogs | Forums

Want to join tables, and fetch from an activity table!


#1

I have this query

select
u1.user_name as user_name being followed,
u2.user_name as follower_name that followes,
ua.*
from users u1
join users_followers uf on uf.user_id=u1.user_id
join users u2 on u2.user_id = uf.follower_id
join user_activity ua on ua.user_id = u2.user_id
where u1.user_name='demo'
order by ua.activity_timestamp

But its not working the way i want...

Right now I get all activity from users who follows user "demo", but i want all activity for the users that demo follows.

As you can se on this fiddle: http://sqlfiddle.com/#!9/59adf/4

Steve follows demo and test. So from table user_activity i want to fetch activity for demo and test. So the fetch should be something like: "posted", "Uploaded image", "posted"

Ho do i turn this query around?

Help much appreciated!


#2

See if this is what you are looking for

select
u1.user_name as user_name ,
u2.user_name as follower_name ,
ua.*
from
users u1
inner join
users_followers uf
on u1.user_id = uf.user_id
inner join
users u2
on u2.user_id = uf.follower_id
inner join
user_activity ua
on ua.user_id = u2.user_id
where u2.user_name='demo'
order by ua.activity_timestamp


#3

It gives the same result :confused:


#4

Please post the output which you want


#5

If you would take the time to read my first post you would notice the SQLfiddel: http://sqlfiddle.com/#!9/59adf/4

And you would also notice: "So the fetch should be something like: "posted", "Uploaded image", "posted""

I want to fetch data from table user_activity with the dependency from users_followers.

In table users_followers, User1 follows user2 and user3.

User2 makes a "post"
User3 "uploads image"
user2 makes another "post".

And this will be fetch from table users_activity, only because user1 folllowes user2 and user2, from table users_followers.