CODEWITHSUNDEEP
sql
Dymond
Dymond

Reputation: 2277

Get user by id from different tables

I have a twitter lookalike site for studying purpose, where you can follow users. Now I want tho fetch all the users that follow the logged in person.

The following table has three rows. id, user_id, follow_id

Now if user 1 whats to follow user 2. the values in the rows would be user_id = 1 , follow_id= 2

But when I'm trying to fetch the users that have the same follow_id, i get only the user_id 2 in return. And not all the users that have follow_id=2

Any idea ?

SELECT 
    user.id, user.username, user.email,
    userdetails.profile_img, following.follow_id 
FROM 
    user 
JOIN 
    userdetails ON user.id = userdetails.user_id 
JOIN 
    following on user.id = following.follow_id 
WHERE 
    following.follow_id = 2

This is how my table looks like

Following
id | user_id | follow_id

Tweets
user_id|id|date|message

user
id|email|password|username

userdetails
id|firstname|lastname|profile_img|user_id|about

Any tips ?

Upvotes: 0

Views: 220

Answers (3)

Dan Baker
Dan Baker

Reputation: 1827

SELECT user.id, user.username,user.email,
userdetails.profile_img, following.follow_id FROM user JOIN userdetails 
ON user.id = userdetails.user_id JOIN following 
ON user.id = following.follow_id WHERE following.follow_id = 2

To get all the ids of users that are matched to follow_id = 2:

SELECT user_id from following where follow_id = 2;

(I would change them name of user_id column in following table to follower_id)

To also get data off userdetails table associated with user_id:

SELECT userdetails.*, user.* FROM userdetails JOIN users 
ON user.id = userdetails.user_id JOIN following
ON user.id = following.***user_id*** WHERE following.follow_id = 2;

See if that works, I think your problem was that you joined following. follow_id to user instead of following.user_id

Upvotes: 1

Sachin
Sachin

Reputation: 40970

Try this

SELECT * FROM Following 
INNER JOIN Users on Following.UserId=Users.Id
INNER JOIN UsersDetails on Users.Id=UserDetails.UserId
where Following.Follow_id=@UserId

of-course you can select column as you want.

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Change JOIN following on user.id = following.follow_id with JOIN following on user.id = following.user_id.

SELECT 
    user.id, user.username, user.email,
    userdetails.profile_img, following.follow_id 
FROM 
    user 
JOIN 
    userdetails ON user.id = userdetails.user_id 
JOIN 
    following on user.id = following.user_id 
WHERE 
    following.follow_id = 2

Upvotes: 2

Related Questions