tags:

views:

70

answers:

3

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.

+1  A: 

Like this:

SELECT * FROM POSTS
WHERE user_id IN (SELECT user_id FROM following
    WHERE follower_id = @your_id)
RBarryYoung
I think a join may yield better performance.
Hosam Aly
Generally no. This was a performance "bug" in SQL Server 2000 (and possibly other SQLs) that many user's of that version have mistakenly come to believe is a general rule of thumb for all of SQL. There is no logical reason for IN to be slower than a join (it actually has to do less work) and as Joe Celko wrote several years ago, generally it isn't. Even on SQL Server 2005+, IN-based subselects are almost always exactly the same as a join or they have only marginal performance difference.
RBarryYoung
Thanks for the information. Is this also true for `exists`-based subselects?
Hosam Aly
Thank you for your answer Barry
ivan73
Hosam, thanks for your comment
ivan73
Hosam: Even more so. IF there is any difference in performance (there often isn't), it's usually that EXISTS is faster than JOIN or IN, because unlike them it can stop as soon as it gets a single record, it doesn't even have to actually retrieve the record: if it can match the conditions from an index, it can return right then. Still, there are always some exceptions.
RBarryYoung
+2  A: 

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

Rob Farley
Thank you for your answer Rob.
ivan73
+1  A: 

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)
gbn
Thank you for your answer and explanation gbn :)
ivan73