CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
Ahmad
Ahmad

Reputation: 117

Get data from 2 tables in mysql (Need Help)

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

Upvotes: 2

Views: 533

Answers (4)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

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

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270727

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
)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

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);

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36451

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

Upvotes: 2

Related Questions