CODEWITHSUNDEEP
c#sqlsql-serverlinq
Mohib Irshad
Mohib Irshad

Reputation: 1990

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

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 |

Upvotes: 1

Views: 845

Answers (3)

tinonetic
tinonetic

Reputation: 8034

Using a LEFT JOIN without a sub-SELECT:

SELECT
   l.id,
   l.user_id,
   l.latitude,
   l.longitude
FROM
   Location l
      LEFT JOIN Task t
         ON l.user_id = t.user_id
WHERE
      t.id IS NULL  /* No record in tasks table */
  OR (t.id IS NOT NULL AND l.status = 1) /* if records exists then all of them must have status equals to 1 */

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Your conditions are equivalent to saying that no non-"1" value exists in task. I would write this as:

select l.*
from location l
where not exists (select 1 from tasks where t.user_id = l.user_id and t.status = 1);

I prefer not exists to not in because not in will filter out all rows if user_id is ever NULL in tasks.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 1

Related Questions