tags:

views:

22

answers:

3

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
+1  A: 

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) )
Cem Kalyoncu
I am assuming parent_id is nullable
Cem Kalyoncu
yes it is nullable, and your query does the job. thanks, I will read more about coalesce
m1k3y02
A: 

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

Kel
child.available is not required, since it does not care if available itself.
Roopesh Shenoy
Do you mean, "parent.id IS NULL" is not required?
Kel
no i meant the statement - child.available = 1
Roopesh Shenoy
+1  A: 
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))
Roopesh Shenoy
I am guessing that this query will be the faster than a join, but not particularly sure about that.
Roopesh Shenoy
thanks Roopesh it's simplier and runs fine :-)
m1k3y02