CODEWITHSUNDEEP
sqlsql-server
sjallamander
sjallamander

Reputation: 439

Check if any children exist

Considering these two tables:

Position(positionid, positiontext, reportstopositionid)

User(userid, positionid)

How can I check if a user has any subordinates in one query? Is it even possible?

A subordinate: user (a) with positionid has at least one or more subordinates if there exists any users (b) with the positionid of user (a) as reportstopositionid to users (b) corresponding positionid

Upvotes: 0

Views: 202

Answers (3)

Vadim Tychonoff
Vadim Tychonoff

Reputation: 881

This will return users who have subordinates: 

SELECT *
FROM   User u
WHERE  EXISTS (
           SELECT 1
           FROM   Position p
           WHERE  p.reportstopositionid = u.positionid
       )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you want to do this with a where clause:

select u.*
from user u
where u.positionId in (select reportstopositionid from position p)

This gets the list of users who match, without duplicates.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

how about this one?

SELECT  DISTINCT a.*
FROM    user a
        INNER JOIN position b
            ON a.userID = b.reportstopositionID

the records returned by this query are those which has matching IDs userid on position table at column reportstopositionID

Upvotes: 1

Related Questions