Family query in self-join 1

[pwills]

My table is (id, name, parent_id) where parent_id is another row.

I need a "family" query that will list all records that are either siblings or a parent or children of an id

For example, table:

1, Bill, <null>
2, Bob, 1
3, Ben, <null>

So the view family_of_Bill would list Bill and Bob
The view family_of_Ben would just list Ben
The view family_of_Bob would list Bill and Bob

 

[pwills]

The query I have at the moment is:

SELECT id FROM users
WHERE
id='Bill'
OR parent_id='Bill'
OR id=(SELECT parent_id FROM users WHERE id='Bill)

It seems like this might not be the most efficient way to write the query; the sub-select looks like it could be expensive. Any ideas?

 

[rt63]

There seems to be a lot of confusion between id and name in the sample query which you have posted. Assuming that wherever you have written id = 'Bill', you actually meant id = 1 (Bill's id).

The subquery in your code should not be expensive as it will execute only once and will have a result of a single row. An alternate could be to use a self-join as follows

SELECT a.id
FROM users a , users b
WHERE
b.id = 1
and (a.id = 1
OR a.parent_id = 1
OR (a.parent_id = b.parent_id and b.parent_id is not null))

The check for not null b.parent_id is just a safeguard against comparison of two null values being returned as true (depends on server settings ANSI_NULLS).


RT

 

[monikai]

Hej !
is only one family in your table ?
I think that you need two id: for family and for parent.
Kind regards from Warsaw !!!!!
Monika (monikai@yahoo.com)