1

I am having trouble working out how to user Inner Join correctly and targeting other tables for multiple conditions within one MySQL query.

Basically, I am creating a small follower feed for my Website, which outputs new posts from any user that you are following.

Posts Table:

id | user_id | message | timestamp

Follow Table:

id | follower_id | following_id

My Query up to now:

SELECT posts.*
FROM posts
INNER JOIN follows
ON posts.user_id = follows.following_id
WHERE follows.follower_id = 1
ORDER BY id DESC limit 10

How could I add a condition in there to: 1.) Greater than or Less than an ID (posts.id) 2.) Specify that it can also be posted by the User ID of 1 (My own posts)

Of course, the 1 will be replaced by a variable so it is dynamic for the current user session.

Any suggestions here, getting really confused by it.

Improve this question
1
  • As to 2) you can add any condition you want in the join condition (ON ...) - you can just put an OR there. The same goes for 1). You just have to add a parameter to the query from the PHP level. Commented Feb 8, 2015 at 23:04

2 Answers 2

Reset to default
1

To add the condition "1.) Greater than or Less than an ID (posts.id)", you can just add the condition into the query at the end of the WHERE clause using the AND operator. The condition itself is (using greater than as an example):

posts.id > $n

where $n is some integer.

To add the condition "2.) Specify that it can also be posted by the User ID of 1 (My own posts)", you can use an OR operator. The keyword from the condition explanation that you provided is "also", which is why we would use the OR operator instead of the AND operator. The condition itself is:

posts.user_id = $my_user_id

where $my_user_id is the user_id of the user making the query or viewing the feed.

Assuming that you want both condition 1 and condition 2 to be met along with the pre-existing condition of "follows.follower_id = 1", you want to group the conditions using parentheses. It might help you to write out your conditions and how you want to be grouped in plain language. For example:

Show all posts made by me or by someone I follow, and which also have an ID of greater/less than a given number.

These conditions can be represented in SQL with:

WHERE (follows.follower_id = $my_user_id
OR posts.user_id = $my_user_id)
AND posts.id > $n

The completed query would be:

SELECT posts.*
FROM posts
INNER JOIN follows
ON posts.user_id = follows.following_id
WHERE (follows.follower_id = $my_user_id
OR posts.user_id = $my_user_id)
AND posts.id > $n
ORDER BY id DESC
LIMIT 10;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you very much for the clear example, exactly what I needed! Was getting confused as to how it would handle the conditions separately, makes sense now though.
No problem! I added a bit more to the answer to explain why you would choose those operators and that grouping. I tried to add enough detail to be useful without being overwhelming, but if there's a part of this answer that's confusing, I'll try to clarify it.
0

You can add an OR clause to the ON join clause for posts.user_id = 1 and you can also put an AND on the WHERE clause for posts.id > ### as needed.

Improve this answer

1 Comment

But adding the OR to the ON join clause would clash with the WHERE follow.follower_id = .. wouldn't it? Could you please provide an example, this is where I am getting confused with it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.