CODEWITHSUNDEEP
mysqlsql
Taylor Foster
Taylor Foster

Reputation: 1215

Find values in one column that share same value in different column without knowing the 2nd cols value

Given This Table: 

Relationship

managerId  companyId
   12         33
   19         33
   27         44
   21         33
    4         20

Is there a way to find all managerId's that share the same companyId but only by knowing ONE of the managerId's and not knowing the companyId

So for example, if we only know that the managerId is 12

SELECT companyId
FROM Relationship
WHERE managerId = 12

We will obviously get 33 back. But within the same query is there a way to get back all managerId's where the companyId is the value of the return from that first statement. So in this case just by knowing managerId=12 I want to get back 12,19,21.

Upvotes: 3

Views: 50

Answers (3)

DCR
DCR

Reputation: 15698

try this:

select managerId from relationship
where companyId = select companyId from relationship where managerId = 12;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Here is one method:

select r.*
from relationship r
where r.companyid = (select r2.companyid from relationship r2 where r2.managerid = 12);

Note: This particular method assumes that relationship.managerid is unique. This seems reasonable given your problem and sample data.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425318

Join the table to itself on companyId:

select b.managerId
from relationship a
join relationship b on b.companyId = a.companyId
where a.managerId = 19

Upvotes: 5

Related Questions