Get data from 2 tables in mysql (Need Help)

Question

I have two tables..

1st Table: post

|post_id  |    post_data |
..........................
|   1     |    any data  |
|   2     |    any data  |
|   3     |    any data  |

2nd Table: post_likes

|like_id  |    post_id   | by_user |
....................................
|   1     |      1       |   3     |
|   2     |      3       |   3     |

when ever a user like any post data is stored in posts_likes table.. i want to show that posts (from both tables once) that are not liked by user 3..

i am using this query

SELECT *
FROM post, post_likes
WHERE post.post_id != post_likes.post_id
AND by_user=3

it showing me these results..

post_id     post_data   like_id     post_id     by_user
  1         my data       2            3          3
  2         my data       1            1          3
  2         my data       2            3          3
  3         my data       1            1          3

But it should show the result of post_id=2 only (because post_id 1 and 3 are liked by user)

what will be the correct query by which i can get those posts that are not liked by user 3

Take a look at this answer: http://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server/4715847#4715847 — Daan Timmer, Feb 13 '12 at 19:23

score 2 · Answer 1 · answered Feb 13 '12 at 19:21

2

select *
from posts
where post_id not in
(
    select post_id
    from post_likes
    where by_user = 3
)

answered Feb 13 '12 at 19:21

Christian Specht

35,843
15
128
182

Michael Berkowski · Accepted Answer · 2012-02-13T19:27:57.073

0

You can use a NOT IN ()subquery in your WHERE clause:

SELECT
  posts.post_id,
  posts.post_data,
  post_likes.like_id,
  post_likes.by_user
FROM posts LEFT JOIN post_likes ON posts.post_id = post_likes.post_id
WHERE posts.post_id NOT IN (
  SELECT DISTINCT post_id FROM post_likes WHERE by_user = 3
)

edited Feb 13 '12 at 19:27

answered Feb 13 '12 at 19:21

Michael Berkowski

267,341
46
444
390

Are you missing an alias? Or else, do you mean table `post_likes` instead of `likes`? – Ted Hopp Feb 13 '12 at 19:24

score 0 · Answer 3 · answered Feb 13 '12 at 19:22

SELECT *
    FROM post p
        LEFT JOIN post_likes pl
            ON p.post_id = pl.post_id
    WHERE NOT EXISTS (SELECT 1
                          FROM post_likes pl2
                          WHERE pl2.post_id = p.post_id
                              AND pl2.user_id = 3);

score 0 · Answer 4 · edited Jul 26 '12 at 13:36

0

SELECT *
FROM post p
WHERE NOT EXISTS (SELECT * 
                  FROM post_likes 
                  WHERE p.post_id = post_likes.post_id 
                    AND user_id = 3
                 )

edited Jul 26 '12 at 13:36

bluish

26,356
27
122
180

answered Feb 13 '12 at 19:23

Bassam Mehanni

14,796
2
33
41

Get data from 2 tables in mysql (Need Help)

4 Answers4