CODEWITHSUNDEEP
mysqlsql
Dymond
Dymond

Reputation: 2277

Get results from two tables from id and following id

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

Answers (3)

PM 77-1
PM 77-1

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

bobs
bobs

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

juuga
juuga

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

Related Questions