CODEWITHSUNDEEP
mysqlsql
Orangeman555
Orangeman555

Reputation: 1181

Select column if results/row not exists?

I'm trying to select a list of post_ids from one column based on whether or not a user_id exists in another.

The idea is simply to get a list of post_ids that a user has not yet interacted with.

The table is like this with it's contents contents: 

+----+---------+---------+--------+---------------------+------------+------+
| id | post_id | user_id | rating | last_update         | num_images | url  |
+----+---------+---------+--------+---------------------+------------+------+
|  1 |    2938 |       5 |      1 | 2014-06-12 22:54:31 |       NULL | null |
|  2 |    2938 |       1 |      1 | 2014-06-12 22:54:54 |       NULL | null |
|  3 |    2940 |       6 |      1 | 2014-06-12 23:36:25 |       NULL | null |
|  4 |    2943 |       3 |      0 | 2014-06-12 23:39:29 |       NULL | NULL |
+----+---------+---------+--------+---------------------+------------+------+

My attempt was this: 

SELECT Distinct post_id
FROM `table`
WHERE user_id !=1

Yet I am still getting results that still gives results where the user has already been connected with the post -- it just excludes the entry including the user.

How do I get results on the condition that the user_id has not been connected with any instance of post_id in the compared column?

Upvotes: 0

Views: 59

Answers (3)

hack69
hack69

Reputation: 1

First you need to find out list of post_id for the given user_id and then omit those post ids.

Try this:

select distinct post_id 
from table 
where post_id not in (select post_id from table where user_id=1);

Upvotes: 0

DMorillo
DMorillo

Reputation: 86

My proposal

SELECT Distinct post_id 
FROM `table` T
WHERE post_id NOT IN (
    /*select posts NOT to be shown */
    SELECT post_id 
    FROM `table` T1
   /* naming the tables differently allows you to make operations 
      with data of the inner selection -T1- AND the outer one -T-   
    */
    WHERE T1.user_id = 1
)

Upvotes: 1

Sharmi
Sharmi

Reputation: 79

Your query should be :

select distinct(post_id )
from tableName
where post_id not in(select post_id from tableName where user_id=1);

Upvotes: 1

Related Questions