id number which has no same value in name

[dotJoon]

code

(id) name
(2) Jane
(3)  Nancy
(4) John
(6) Ted
(7) 6
(8) Tom
(9) 3

I have the above data in myTable2.
The following code will produces the following result.

code2
select id, name
from myTable2
where id=2 
target2
(2) Jane

code3
select id, name
from myTable2
where id=3 
target3
(3)  Nancy

code4
select id, name
from myTable2
where id=4 
target4
(4) John

code6
select id, name
from myTable2
where id=6 
target6
(6) Ted

code8
select id, name
from myTable2
where id=8 target8
(8) Tom

The following code doesn't work correctly, but I hope that it shows what I want.

code2
select id, name
from myTable2
where id=2 and which has not 2 in column name
result2
(2) Jane

code3
select id, name
from myTable2
where id=3 and which has not 3 in column name
result3
none

code4
select id, name
from myTable2
where id=4 and which has not 4 in column name
result4
(4) John

code6
select id, name
from myTable2
where id=6 and which has not 6 in column name
result6
none

code8
select id, name
from myTable2
where id=8 and which has not 8 in column name
result8
(8) Tom

Can I get my target result in mySQL 4.0 with your help?

[longneck]

joon, this is the same question you asked previously, but not as complex. self-join the table.

[dotJoon]

code
select 
       t1.id,
       t1.name
       from myTable2 t1
left outer
  join myTable t2
    on t1.id = t2.name
where 
  t1.name not regexp '^[[:digit:]]+$' 


result

(2) Jane
(3) Nancy
(4) John
(6) Ted
(8) Tom

target result

(2) Jane
(4) John
(8) Tom

How can I change the code above for getting my target result above?

[longneck]

ah, very good start. your left outer join finds all of the records in table t2 where the name is the same as the record in t1. if it can't find any matching records, it will return a null. with that in mind, what do you have to add to the where clause to filter out the records you don't want?

hint: add t2.name to your field list and run your query. see if there is a pattern you can test for.

[dotJoon]

where t1.name not regexp '^[[:digit:]]+$' 
and t2.name not regexp '^[[:digit:]]+$'

the aobve where clause doesn't work.

[longneck]

what was the output of the query when you added t2.name?

-or-

hint: you're testing for the wrong thing.

[dotJoon]

what was the output of the query when you added t2.name?

output
(2) Jane
(3) Nancy
(4) John
(6) Ted
(8) Tom

hint: you're testing for the wrong thing.

what is the right thing?
I like to remove the records which have child records.

[r937]

joon, why do the same problems seem to keep coming up over and over?

http://www.sitepoint.com/forums/showthread.php?t=280781

[dotJoon]

joon, why do the same problems seem to keep coming up over and over?

http://www.sitepoint.com/forums/showthread.php?t=280781

You see they are same problems. I see they are differnt problems.
Please see in the height of my eye line.

Do you mean the following code?

code
 
select 
       t1.id,
       t1.name
       from myTable2 t1
left outer
  join myTable t2
    on t1.id = t2.name
where 
   t1.name not regexp '^[[:digit:]]+$'  and t2.id is null
order by id

result

(2) Jane
(3) Nancy
(4) John
(6) Ted
(8) Tom
(2) Jane
(3) Nancy
(4) John
(6) Ted
(8) Tom

target result

(2) Jane
(4) John
(8) Tom

[r937]

i don't mean that they are exactly the same

all your problems are subtly different, but they are all similar because they all involve fundamental things like a LEFT OUTER JOIN with a test for NULL in the right table, a basic technique that you seem to have a hard time understanding -- or, at least, a hard time remembering

[dotJoon]

i don't mean that they are exactly the same

all your problems are subtly different, but they are all similar because they all involve fundamental things like a LEFT OUTER JOIN with a test for NULL in the right table, a basic technique that you seem to have a hard time understanding -- or, at least, a hard time remembering

Okay, I do have a question to you at this moment.
Does the my code above have a problem in only where clause in getting my target result?

[r937]

Does the my code above have a problem in only where clause in getting my target result?

i don't know what you're trying to do, and i cannot look at it right now to try to understand what you're trying to do

maybe tomorrow, if you haven't figured it out by then

[dotJoon]

select 
       t1.id,
       t1.name
       from myTable2 t1
left outer
  join myTable2 t2
    on t1.id = t2.name
where 
   t1.name not regexp '^[[:digit:]]+$'  and t2.id is null 
order by id

I think I got the answer.

I had a typo. I am very sorry on my fault.

[longneck]

...a test for NULL in the right table, a basic technique that you seem to have a hard time understanding -- or, at least, a hard time remembering

i understand that you might not see things like this right away. i thikn it's because you focus on the final results too much. you need to take this in small steps, like so:

ok, so i want to get a list of the people and their id numbers:

select t1.id
     , t1.name
  from myTable t1

i now want to eliminate rows that have an id number that matches any row in the name column, so i will start by matching those rows with a left outer join. i don't know how to filter them yet, but i know i will need to join the table, so let's just join the table first and see what i get:

select t1.id
     , t1.name
     , t2.id
     , t2.name
  from myTable t1
left outer
  join myTable t2
    on t1.id = t2.name

and when you get the results, you will see the pattern. but if you try to jump straight to the answer, it won't work.

how about this: when you look at this equation, can you tell me the expanded form in one step? (x-3)(x+2)(y+4) i bet not! you have to multiply out on pair, then the other. it's the same with SQL: you have to do step 1, then step 2. until you start breaking all of your problems down, you'll never see it.

[dotJoon]

i understand that you might not see things like this right away. i thikn it's because you focus on the final results too much. you need to take this in small steps, like so:

ok, so i want to get a list of the people and their id numbers:

select t1.id
     , t1.name
  from myTable t1

i now want to eliminate rows that have an id number that matches any row in the name column, so i will start by matching those rows with a left outer join. i don't know how to filter them yet, but i know i will need to join the table, so let's just join the table first and see what i get:

select t1.id
     , t1.name
     , t2.id
     , t2.name
  from myTable t1
left outer
  join myTable t2
    on t1.id = t2.name

and when you get the results, you will see the pattern. but if you try to jump straight to the answer, it won't work.

how about this: when you look at this equation, can you tell me the expanded form in one step? (x-3)(x+2)(y+4) i bet not! you have to multiply out on pair, then the other. it's the same with SQL: you have to do step 1, then step 2. until you start breaking all of your problems down, you'll never see it.

Thank you for your advice.