MySQL display values from another table

Vote count:

0

I need your help. Basically, I have 4 tables namely: posts, users, friendship and favourite_posts.

I need to select all posts of a user and his friends' posts. Also I need to include in the result set the favourited posts.

So far this is what I've done:

 SELECT DISTINCT(p.title), p.post_id, p.user_id, fp.post_id
 FROM posts AS p
 LEFT OUTER JOIN favourite_posts AS fp
 ON p.post_id = fp.post_id
 JOIN users AS u
 ON p.user_id = u.user_id
 LEFT JOIN friendship AS f
 ON p.user_id = f.user_id
 OR p.user_id = f.friend_id
 WHERE p.user_id = 1
 OR f.friend_id = 1
 AND fp.user_id = 1
 AND f.status = 1

SCHEMA:

CREATE TABLE posts
(`post_id` int, `title` varchar(11), `user_id` int);

INSERT INTO posts
(`post_id`, `title`, `user_id`)
VALUES
(1, 'Hello World', 1),
(2, 'Hola Mundo', 2),
(3, 'Ola Mundo', 1),
(4, 'Hi Mundo', 4),
(5, 'Test 1', 1),
(6, 'Test 2', 2),
(7, 'Test 3', 3),
(8, 'Test 4', 4),
(9, 'Test 5', 1),
(10, 'Test 6', 2);


CREATE TABLE users
(`user_id` int, `name` varchar(7));

INSERT INTO users
(`user_id`, `name`)
VALUES
(1, 'George'),
(2, 'Michael'),
(3, 'Learns'),
(4, 'To Rock');


CREATE TABLE friendship
(`friendship_id` int, `user_id` int, `friend_id` int, `status` int);

INSERT INTO friendship
(`friendship_id`, `user_id`, `friend_id`, `status`)
VALUES
(1, 1, 2, 1),
(2, 1, 3, 1),
(3, 2, 1, 1),
(4, 2, 4, 1);


CREATE TABLE favourite_posts
(`fp_id` int, `post_id` int, `user_id` int);

INSERT INTO favourite_posts
(`fp_id`, `post_id`, `user_id`)
VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 1),
(4, 2, 4);

You can test my SQLFiddle here

In my example, I need to get user # 1's all posts (including his friends' and favourited posts)

The result should have total count of 8 (i.e. 8 posts) but I'm getting only 5. What do you think is wrong with my query? I would gladly appreciate any kind of help. Thanks!

asked 1 min ago

bEtTy Barnes

1,166

MySQL display values from another table