>With JOIN and DISTINCT also. But you have to add DISTINCT.
>
Actually, you're not arguing original Sergey's statement. He said that LEFT JOIN can be always replaced with NOT EXISTS. In your case it's INNER JOIN and DISTINCT (we're looking for parents that HAVE children).
In case of looking for parents that don't have children NOT EXISTS can be replaced with the LEFT JOIN.
>
>>You just confirmed that it can be done with LEFT JOIN with DISTINCT.
>>
>>>
>>>Ok, There is a parent table and a child table. The child table contains a link to its parent
>>>
>>>
>>> create cursor parent ;
>>> ( p_id I, ;
>>> p_name c(32) ;
>>> )
>>>
>>> insert into parent values (1, 'Father Tom')
>>> insert into parent values (2, 'Father Mike')
>>>
>>> create cursor Child ;
>>> ( c_id I, ;
>>> c_p_id I, ;
>>> p_name c(30) ;
>>> )
>>>
>>> insert into Child values (1, 2, 'Daniel')
>>> insert into Child values (2, 2, 'David')
>>>
>>>
>>>I want to find the parents that have children
>>>
>>>
>>>(1) With exists
>>>
>>> select * ;
>>> from Parent ;
>>> where ( exists ;
>>> ( select 1 from Child where c_p_id == p_id ) ;
>>> ) ;
>>> into cursor ParentsWithChildren
>>>
>>>
>>>(2) How would you get the same result with JOIN or LEFT JOIN ? - no fiddling with DISTINCT
If it's not broken, fix it until it is.
My Blog