Reputation: 2277
Since I am quite pixilated sometimes I asked wrong question before. Now this is what I'm trying to accomplish.
Im trying to get the values from user.id = 1
and the values of the user that user.id = 1
is following.
But I ether get the results from user.id 1
or in this case the followed following.follow_id = 2
.
Any ideas ?
I have created a SQLfiddle for showing. As you see the only result I get is from user_one.
http://sqlfiddle.com/#!2/6a39f/1
SELECT user.email,
user.username,
tweets.message,
tweets.date,
userdetails.profile_img,
userdetails.firstname,
userdetails.lastname,
following.id, following.user_id,
following.follow_id
FROM user
JOIN userdetails ON user.id = userdetails.user_id
JOIN tweets ON userdetails.user_id = tweets.user_id
LEFT JOIN following ON user.id = 1
WHERE following.user_id = 1
Tables:
Tweets
user_id | id | date | message
user
id | email | password | username
userdetails
id | firstname | lastname | profile_img | user_id | about
following
id | user_id | follow_id
In this case following table has value
user_id = 1 and follow_id = 2
summary .
User_id = 1 is following follow_id = 2
But i only get results from user_id = 1
and not from both.
Thanks.
Basicly Im trying to combine this two querys.
Query for getting the results from the user thats been followed.
SELECT user.email,
user.username,
tweets.message,
tweets.date,
userdetails.profile_img,
userdetails.firstname,
userdetails.lastname,
following.id, following.user_id,
following.follow_id
FROM user
JOIN userdetails ON user.id = userdetails.user_id
JOIN tweets ON userdetails.user_id = tweets.user_id
JOIN following ON following.follow_id
WHERE following.follow_id = tweets.user_id AND following.user_id = '1' ORDER BY tweets.date DESC
And the query for fetching the value of user 1
SELECT user.email,
user.username,
tweets.message,
tweets.date,
userdetails.profile_img,
userdetails.firstname,
userdetails.lastname
FROM user
JOIN userdetails ON user.id = userdetails.user_id
JOIN tweets ON userdetails.user_id = tweets.user_id
WHERE user.id = '1' ORDER BY tweets.date DESC
Upvotes: 3
Views: 359
Reputation: 13352
This is my (slightly) modified version of bobs
's answer (added LEFT OUTER JOIN
and modified the sub-query):
SELECT user.id,
user.email,
user.username,
tweets.message,
tweets.date,
userdetails.profile_img,
userdetails.firstname,
userdetails.lastname,
following.id, following.user_id,
following.follow_id
FROM user
LEFT JOIN following ON user.id = following.user_id
JOIN userdetails ON user.id = userdetails.user_id
JOIN tweets ON userdetails.user_id = tweets.user_id
WHERE user.id=1 OR
user.id IN (SELECT follow_id
FROM following
WHERE following.user_id = 1);
Here's my SQL Fiddle with 3rd user added. Please see if it works for your real data set.
Upvotes: 1
Reputation: 22224
It appears that you want to get data where the ID is 1 or the ID is one or more other values. So, you might start with this
SELECT user.email,
user.username,
tweets.message,
tweets.date,
userdetails.profile_img,
userdetails.firstname,
userdetails.lastname,
following.id, following.user_id,
following.follow_id
FROM user
JOIN userdetails ON user.id = userdetails.user_id
JOIN tweets ON userdetails.user_id = tweets.user_id
WHERE user.id = 1
OR user_id IN
(
SELECT follow_id
FROM following
WHERE user.id = following.user_id
)
Upvotes: 2
Reputation: 1314
Does it help if you rewrite the last two lines as
LEFT JOIN following ON following.user_id = user.id
WHERE user.id = 1
I don't think the ON clause of your following table's left join will work as it is now.
Upvotes: 0