mysql join incorrect results

[MDanz]

Each childrow has a parentid and position. For childrows with the same position there is one row where start='1'.

What i'm trying to do is return the pending rows with their start row.

The results that should be shown are; start(owen) pending(dave,paul). This is because they have the same position. Here is the SQL fiddle http://sqlfiddle.com/#!2/e6e54/1

  id |  name | parentid|  position|  start  |  pending |
   1 |  mike |    0    |    0     |    0    |     0    |
   2 |  dave |    1    |    1     |    0    |     1    | 
   3 |  paul |    1    |    1     |    0    |     1    |
   4 |  john |    1    |    2     |    1    |     0    |
   5 |  bret |    1    |    2     |    0    |     0    |
   6 |  owen |    1    |    1     |    1    |     0    |
   7 |  rick |    1    |    3     |    1    |     0    |
   8 |  jaye |    1    |    3     |    0    |     0    |



    $getquery = mysql_query("select child.*
from `mytable` child inner join `mytable` parent 
on parent.id=child.parentid
inner join `mytable` child2 on child.parentid=child2.parentid
and child2.pending='1'
where child.start='1' ORDER BY child.id DESC");



while($row=mysql_fetch_assoc($getquery)) {

 $name = $row['name'];

echo "<p>Name: $name </p>";

}

[rch1231]

Hello,

I see a couple of things that are causing your problem. First of all you are only selecting fields from the child table to be displayed so the join servves no purpose. Try adding a field from the partent table to the output and get the first join providing the results you want then use the HAVING post selection condition to just select the records that meet the second condition:

$getquery = mysql_query("select  child.id as child_ID, 
child.`name`, parent.id as parent_ID, child.position,
child.start, child.pending
from `mytable` child inner join `mytable` parent
on parent.id=child.parentid
where child.start='1' ORDER BY child.id DESC
HAVING pending=1");

Or something along those lines should work.