SQL to get messages only from people I follow.

Question

Hi guys,

I'm wondering how to create SQL query to get messages only from people I follow. I assume that create a SQL join query is the right way but I don't know how. Please could someone help me?

I have these three tables now:

CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 255 ) NOT NULL ,
`email` VARCHAR( 255 ) NOT NULL ,
`password` VARCHAR( 8 ) NOT NULL ,
`status` ENUM( 'active', 'inactive' ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `posts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_id` INT NOT NULL ,
`body` VARCHAR( 140 ) NOT NULL ,
`stamp` DATETIME NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `following` (
`user_id` INT NOT NULL ,
`follower_id` INT NOT NULL ,
PRIMARY KEY ( `user_id` , `follower_id` )
) ENGINE = MYISAM ;

Thank you very much for your reply.

Answer 1

Like this:

SELECT * FROM POSTS
WHERE user_id IN (SELECT user_id FROM following
    WHERE follower_id = @your_id)

Answer 2

Try this:

select p.body, p.stamp, pu.username
from posts p
  join
  following f
  on f.user_id = p.user_id
  join
  users you
  on you.user_id = f.follower_id
  join
  users pu
  on pu.user_id = p.user_id
where you.username = 'obadja7'
;

...or something like it.

Rob

Answer 3

Now you have 3 answers: JOIN, IN, EXISTS

SELECT *
FROM POSTS p
WHERE EXISTS (SELECT *
    FROM following f
    WHERE f.follower_id = @your_id AND f.user_id = p.user_id)

I'd use EXISTS because generally:

• it gives the same plan (as JOIN + IN, depends on duplicates)
• supports composite keys (IN does not)
• there is no need to de-duplicate output (eg JOIN)