[b]data in myTable[/b]
(id) name
(2) Jane
[COLOR=Red](3) [/COLOR] Nancy
(4) John
[COLOR=Red](6)[/COLOR] Ted
(7) [COLOR=Red]6[/COLOR]
(8) Tom
(9) [COLOR=Red]3[/COLOR]
I have the above data in myTable.
The following code will produces the following result.
[b]code[/b]
select
t1.id,
t1.name
from myTable t1
left outer
join myTable t2
on t1.id = t2.name
where
t1.name not regexp '^[[:digit:]]+$' and t2.id is null
[b]result[/b]
(2) Jane
[COLOR=Red](3) Nancy[/COLOR]
(4) John
[COLOR=Red](6) Ted[/COLOR]
(8) Tom
The following would-be code doesn't work correctly, but I hope it shows what I want.
[b]would-be code[/b]
select
t1.id,
t1.name
from myTable t1
left outer
join myTable t2
on t1.id = t2.name
where
[B] all records which have [COLOR=Red]no[/COLOR] self-joined records[/B]
[b]target result[/b]
(2) Jane
(4) John
(8) Tom
Can I get my target result above in mySQL 4.0 with your help?
