Help with Left Join

[MDanz]

I'm doing a left join query for the table below and it is not getting the correct results.  It should return the results peter, john and david  because they have the same sid and the wildcard is peter.

 

Instead it is returning carl, peter, john and david.  What do i have to change to correct this?

 

SELECT child.* FROM mytable child LEFT JOIN mytable parent on parent.sid=child.sid WHERE parent.name LIKE '%peter%' ORDER BY child.id ASC

 

idsidnameparent

0nullmike[/td]

[td]1

0rickmike

21carlrick

31peterrick

41johnrick

51davidrick

[PFMaBiSmAd]

Add the following -

 

AND parent.name != child.name

[MDanz]

i tried that and now it is removing peter from results.  So the results are carl, john and david.

 

SELECT child.* FROM mytable child LEFT JOIN mytable parent on parent.sid=child.sid WHERE parent.name LIKE '%peter%' AND parent.name != child.name ORDER BY child.id ASC

[PFMaBiSmAd]

Opps, I misread your problem to be that you didn't want the selected name to be included.

 

Is the data you posted correct? Carl has the same sid as the others. There's nothing in what you posted that would exclude carl form the result set.