CODEWITHSUNDEEP
sqlsql-server
fallingdog
fallingdog

Reputation: 192

select records where condition is true in one record

I need to select cid, project, and owner from rows in the table below where one or more rows for a cid/project combination has an owner of 1.

cid | project | phase | task | owner
-----------------------------------
1   | 1       | 1     | 1    | 1
1   | 1       | 1     | 2    | 2
1   | 1       | 1     | 3    | 2
2   | 1       | 1     | 1    | 1
2   | 1       | 1     | 2    | 1
3   | 1       | 1     | 3    | 2

My output table should look like the this:

cid | project | phase | task | owner
-----------------------------------
1   | 1       | 1     | 1    | 1
1   | 1       | 1     | 2    | 2
1   | 1       | 1     | 3    | 2
2   | 1       | 1     | 1    | 1
2   | 1       | 1     | 2    | 1

The below query is what I came up with. It does seem to test okay, but my confidence is low. Is the query an effective way to solve the problem?

select task1.cid, task1.project, task1.owner
from 
(select cid, project, owner from table) task1
right join 
(select distinct cid, project, owner from table where owner = 1) task2
on task1.cid = task2.cid and task1.project = task2.project

(I did not remove the phase and task columns from the sample output so that it would be easier to compare.)

Upvotes: 1

Views: 1488

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You can simply use a IN clause 

  select cid, project, owner
  from table
  where cid in (select distinct id from table where owner = 1)

or a inner join with a subquery 

  select a.cid, a.project, a.owner
  from table a 
  INNER JOIN ( select distinct cid , project
        from table where owner = 1
  ) t on t.cid = a.cid and t.project = a.project

Upvotes: 2

Related Questions