Child/Parent table - "family" view 1

[pwills]

[NB. This is a derivative of a question posed on MS Sql Svr forum.]

I have a classic self-referring table
USER(USER_ID,PARENT)

I want to list all users in Bob's "family" i.e. Bob, children, parent, siblings.

Is the following query OK? Is it possible to avoid repeating the sub-select?

SELECT USER_ID FROM USER
WHERE
USER_ID = 'Bob' OR
PARENT = 'Bob' OR
USER_ID = (SELECT PARENT FROM USER WHERE USER_ID = 'Bob') OR
PARENT = (SELECT PARENT FROM USER WHERE USER_ID = 'Bob')

 

[r937]

query looks okay at first glance (it gets bob's family okay, as far as i can tell)

any database worth its salt will see that the two subqueries are the same and not run it twice

you could try collapsing them like this

OR (SELECT PARENT FROM USER WHERE USER_ID = 'Bob')
IN ( USER_ID, PARENT )

rudy