SQL where clause with multiple keys in same table

Question

I have a table which has two keys and I cannot break the table up into two.

The first key, id is a counter. The second key is parent_id.

For example:

id, parent_id, text
1, 0, Hello Steve - this is Bill
2, 1, Hi - how are you Bill?
3, 0, Good morning Janice
4, 3, Hello - good morning to you

The first record is the parent record of the conversation and the second is the child record.

What I'm having difficulty doing is writing a query that returns both records for a single conversation when you pass either id.

For example:

select * from table where id = 2 or parent_id = ( select parent_id from table where id = 2 )
select * from table where id = 1 or parent_id = ( select parent_id from table where id = 1 )

The first query will work, returning the records with id 1 and 2. The second will not as it will return the row with id 3 as well because if you pass 1 you'll get zero for the parent_id.

I'm sure it's something simple that I'm missing due to paralysis by analysis.

Thanks.

Answer 1

Add AND parent_ID <> 0 to your subquery.

Answer 2

-- I *think* from OP's description , this should do it.
-- The first SELECT will _always_ bring back a single row (the ID is unique and known to the issuer of the query).
-- The second SELECT may bring back zero, one or many rows
-- So (if my understanding is correct) in English:
-- Bring back the row for the given ID and all (if any) rows which have me as a parent_id.
-- Rehashed : joining up the IDs from parent->child
-- Again, not tried...
SELECT * FROM table parent WHERE parent.id = <id>
union
SELECT * FROM table child WHERE child.id=parent.id;

Answer 3

This query will retrieve:

• the specified child record, its parent, and its siblings
• the specified parent record and all its single-level children

 SELECT DISTINCT * FROM 
 (
    --is a child of the specified parent
    SELECT * from table  WHERE parent_id = @SomeID 
    UNION ALL
    -- is the record specified by ID
    SELECT * from table  WHERE ID = @SomeID 
                         --and get the parent itself
                         OR ID = (SELECT parent_id FROM table WHERE ID = @SomeID) 

    UNION ALL
    --all siblings with the same parent
    SELECT * FROM table WHERE parent_id = (SELECT parent_id 
                                           FROM table WHERE ID = @SomeID) 
                        AND parent_id>0

   ) F
ORDER BY ID