CODEWITHSUNDEEP
phpmysqlsql
Dubstaphone
Dubstaphone

Reputation: 420

Select all from table where value=multiple values from another table

I'm trying to select posts that are posted by somebody the user is following.

My posts table looks like this:

posts structure

My followers table looks like this:

followers structure

The "creator" column in the posts table is the ID of the user who posted it. The "follower" colum in the followers table is the user who is following the "following" column.

I'm using this query (to no avail) to select all from posts where creator=all users the user is following:

SELECT * FROM posts WHERE creator=(SELECT following FROM followers WHERE follower=the user's id) ORDER BY datetime DESC;

In theory:

SELECT * FROM posts WHERE creator=1 or 2 or 3 or 4...or 998 or 999

etc, etc.

What would be the best way to do this?

Thanks!

Upvotes: 0

Views: 1674

Answers (3)

Hara Prasad
Hara Prasad

Reputation: 724

Try it

SELECT * 
FROM posts p, followers f 
WHERE 
  p.creator=f.following
  and f.follower ='user_id' 
ORDER BY p.datetime DESC;

Upvotes: 1

Rohit Aggarwal
Rohit Aggarwal

Reputation: 650

Using joins in this case can boost up the performance of your query. I recommend the following:

SELECT * FROM posts p
INNER JOIN followers f 
ON p.creator = f.following
WHERE f.follower = <user_id>
ORDER BY datetime DESC;

Upvotes: 1

Vickrant
Vickrant

Reputation: 1303

Try changing the query to:

SELECT * FROM posts WHERE creator in (SELECT following FROM followers WHERE follower=the user's id) ORDER BY datetime DESC;

Upvotes: 4

Related Questions