CODEWITHSUNDEEP
phpmysql
Aaron Wojnowski
Aaron Wojnowski

Reputation: 6480

Select statement where id is in other table

So I have two tables setup:

=========================
|        Shares         |
=========================
| user_id | other stuff |
=========================
| 1       | bla bla bla |
| 2       | bla bla bla |
=========================

=====================================
|             Followers             |
=====================================
| id   | follower_id | following_id |
=====================================
| 1    | 7           | 1            |
| 2    | 7           | 2            |
=====================================

What I want to do is create a feed which displays the "shares", but only for the people that you are following. Ideally I'd like to do this with one query, as well.

Here an (obviously incorrect) thing that I tried. For the sake of this example, assume :user_id is the current user (or follower's) ID.

select * from shares where count(select * from followers where follower_id = :user_id and following_id = share.id) > 0

Upvotes: 0

Views: 82

Answers (1)

paxdiablo
paxdiablo

Reputation: 881283

You can join the tables with something like:

select s.* from shares s, followers f
where f.follower_id = :user_id
  and s.user_id = f.following_id

That's using the implicit join syntax and many people prefer the explicit variant, but it's not really necessary in this simple case.

Upvotes: 2

Related Questions