mysql: take all records with meeting_id = x and it's parent is available but don't care if available itself ?

Question

how do i select all records matching following criteria:

take all records with meeting_id = x AND it's parent is available OR doesn't have parent but don't care if available itself ?

table people:  
id, name, address, meeting_id, available, parent_id

Answer 1

Untested, pls try. If no parent coalesce will select second param and use automatic 1.

SELECT * FROM people 
  WHERE 
    meeting_id=x AND 
    ( COALESCE( (SELECT available FROM people AS parent WHERE id=parent_id), 1) )

Answer 2

You may do it using JOIN:

SELECT
  child.*,
  parent.name
FROM
  people child
LEFT JOIN
  people parent on (child.parent_id = parent.id)
WHERE
  (parent.available = 1 or parent.id IS NULL) AND
  child.meeting_id = x;

See http://dev.mysql.com/doc/refman/5.0/en/join.html for details

Answer 3

select * from people outt
  where meeting_id = x and
  (parent_id is null or exists (select 1 from people inn where inn.id = outt.parent_id and available = 1))