Select all rows from a table except where row in another table with same id has a particular value in another column

Question

I want to make a query that select all rows from a table except where rows in another table with same id as logged in user and has same value in another column.

Table1
id    |    value
=================
1      |    10
2      |    20
3      |    30
4      |    40
5      |    50
6      |    60

-

Table2
id     |    user_ids   |  another_column
=========================================
1      |       2       |       30
2      |       4       |       50
3      |       4       |       60

So if Table2.user_ids = (loggedin userid) and at the same time Table1.value = Table2.another_column - those rows should not be ouputted in the results.

If we say that we have a user with id:4, the user should not see row5 or row6 from Table1 because values matches

Should I go with some kind of subquery or join in the sql?

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Mar 06 '17 at 22:31

score 0 · Accepted Answer · answered Mar 06 '17 at 22:36

0

Taking the user with id 4 from your example above:

select * from Table1
where value not in (
  select another_column
  from Table2
  where user_ids = 4
);

answered Mar 06 '17 at 22:36

Ray O'Donnell

759
4
11

Thank you! this solved my problem – rkdbrors Mar 07 '17 at 17:06

score 0 · Answer 2 · answered Mar 06 '17 at 22:37

0

select  id, value from t1 where value not in (select another_column from t 2 where userid = 4)

something like that

answered Mar 06 '17 at 22:37

user3050803

27
5

Select all rows from a table except where row in another table with same id has a particular value in another column

2 Answers2