SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    id number which has no same value in name

    Code:
    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.
    Code:
    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.
    Code:
    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?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    joon, this is the same question you asked previously, but not as complex. self-join the table.

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:]]+$' 
    
    
    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?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    where t1.name not regexp '^[[:digit:]]+$' 
    and t2.name not regexp '^[[:digit:]]+$'
    the aobve where clause doesn't work.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what was the output of the query when you added t2.name?

    -or-

    hint: you're testing for the wrong thing.

  7. #7
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    what was the output of the query when you added t2.name?

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


    Quote Originally Posted by longneck
    hint: you're testing for the wrong thing.
    what is the right thing?
    I like to remove the records which have child records.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    joon, why do the same problems seem to keep coming up over and over?

    http://www.sitepoint.com/forums/showthread.php?t=280781
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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:
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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
    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?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by joon
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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.

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ...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:
    Code:
    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:
    Code:
    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.

  15. #15
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    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:
    Code:
    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:
    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •