Selecting from more than two tables using join selects duplicates

Question

I have 3 table on my mysql db (user, follower, post), I wrote an sql, that get all posts from the people a user followed.

sql

SELECT post.* FROM post JOIN
       follower ON post.owner_user_id = follower.user_id AND
       follower.follower_id = "3"

result

id | owner_user_id | content
2  | 1             | why are all my senior developers jerks?
3  | 1             | PHP7 in your face node.js

user table

id | username | password
 1 | user1    | 12345678
 2 | user2    | 12345678
 3 | user3    | 12345678

follower table

user_id | follower_id
3       | 1
3       | 2
1       | 3

post table

id | owner_user_id | content
1  | 2             | reading a 1k+ page on mysql, do i need to?
2  | 1             | why are all my senior developers jerks?
3  | 1             | PHP7!, in your face node.js
3  | 3             | I posted

so now am trying to select post of people the user is following and the posts of the user

I tried this sql

sql

SELECT post.* FROM post JOIN
       follower ON post.owner_user_id = follower.user_id AND
       follower.follower_id = "3" JOIN
       user ON post.owner_user_id = user.id= "3"

result

null

Please is what am trying to achieve with the sql possible, if(possible) {"what_am_i_doing_wrong"}();

edits

user_id 3 has a post, still running the above sql returns null, was hoping if the user had no post, only post of the people the user is following is returned

You didn't get anything because there is no value 3 for owner_user_id — Shanu k k, Dec 19 '16 at 11:48
@shanu k k but aren't i suppose to get the post of people the user is following then? — Eddie Dane, Dec 19 '16 at 11:53

score 2 · Accepted Answer · answered Dec 19 '16 at 11:54

2

select post.* from 
(select user_id from follower where follower_id = 3 union select 3) blah 
join post on blah.user_id = post.owner_user_id;

answered Dec 19 '16 at 11:54

slowko

829
1
7
12

Wow, this work like magic literally like magic, i don't have a "blah" in my tables, how the hell did this not return errors, i have got a lot to learn in mysql. OOh and thank you! – Eddie Dane Dec 19 '16 at 12:08
It's an alias. You can change it for a more meaningful identifier. – slowko Dec 19 '16 at 12:10
can i follow you on twitter. or you website. – Eddie Dane Dec 19 '16 at 14:24

Alberto · Answer 2 · 2016-12-19T12:17:45.927

1

Try this:

SELECT Distinct post.* FROM post JOIN
   follower ON post.owner_user_id = follower.user_id JOIN
   user ON follower.user_id = user.id WHERE user.id = 3

You shuldn't use conditions in JOIN statement, use WHERE instead.

edited Dec 19 '16 at 12:17

answered Dec 19 '16 at 11:48

Alberto

1,423
18
32

I tried this but it returned 2 duplicates of user 3's post. and nothing more – Eddie Dane Dec 19 '16 at 12:04
now it just returns the single user 3's post, but am trying to return user 3's post and post of people user 3 follows. siloko's answer did just that, thanks for your answer though i appreciate. – Eddie Dane Dec 19 '16 at 14:37
"You shuldn't use conditions in JOIN statement, use WHERE instead" -- it makes sense to me to put the search conditions in the same place. What's the advantage of putting some in the `JOIN` and others in the `WHERE`? – onedaywhen Dec 20 '16 at 08:32
@onedaywhen Check this: http://stackoverflow.com/a/1019006/6371926 "...may already be excluded during the JOIN process.". I hope this helps u :D – Alberto Dec 20 '16 at 09:06
@AlbertoLópezPérez: I don't understand your point. If we agree that it makes no difference to optimizer, which can rearrange things as it sees fit, and ultimately "it depends, of course", why then do you say "You shuldn't" ? – onedaywhen Dec 20 '16 at 09:15
because the conditions that are inside the joins, can be excluded because is "in the middle of de code" and the where statement is at the end of the query and this condition (in the where) is overall query. – Alberto Dec 20 '16 at 09:25

Selecting from more than two tables using join selects duplicates

2 Answers2