Mohib Irshad Mohib Irshad - 3 months ago 14
SQL Question

Select values from one table based on specific value of another table Linq

I have 2 tables:

Location
id | user_id | latitude | longitude|
1 | 2 | 11.32323 | 11.32323 |
2 | 3 | 12.32323 | 12.32323 |
3 | 4 | 21.32323 | 12.32323 |





Task
id | user_id | status |
1 | 2 | 0 |
2 | 2 | 1 |
3 | 2 | 0 |
4 | 2 | 2 |
5 | 2 | 1 |
6 | 2 | 0 |
7 | 3 | 1 |
8 | 3 | 1 |
9 | 3 | 1 |


I want to select all rows from location table in which users have


  • either no record in Tasks table (for e.g. user_id = 4)

  • or if records
    exists then all of them must have status equals to 1 (for e.g. user_id
    = 3)
    .



In above example, user_id = 2 should not be selected because it has rows in Tasks table with status other than 1.

I am not very much familiar with SQL and LINQ so any help would be appreciated.

This is the expected result:

Result
id | user_id | latitude | longitude|
2 | 3 | 12.32323 | 12.32323 |
3 | 4 | 21.32323 | 12.32323 |



  • Location with user_id = 2 was ignored because it has some rows in Tasks table with status other than 1.

  • Location with user_id = 3 was selected because all rows in Tasks table has status = 1.

  • Location with user_id = 4 was selected because there were no rows in Tasks table with user_id = 4.


Answer

Looking at you requirements could be this

select * from location 
where user_id not in (select distinct user_id from task )
or user_id not in  (select distinct user_id from  task where status != 1);
Comments