Results 1 to 3 of 3

Thread: SQL Query

  1. #1
    Join Date
    Jan 2003
    Posts
    8

    Unanswered: SQL Query

    Hi

    I need a little help in my SQL queries i'm not really sure whether this can be done ...

    I have 3 tables T1,T2,T3

    T1 - contains the id and phone_num
    T2 - contains the id only
    T3 - contains a list of phone numbers

    I compare T2 with T1 to obtain the phone_num that are in T2 using this statement..


    SELECT T1.case_id, T1.phone_no FROM T1 LEFT OUTER JOIN T2 ON T1.case_id = T2.case_id WHERE T2.case_id = T1.case_id


    but now i want to use that result that i obtain ie the phone_num to compare it to T3 which have a whole list of phone numbers. i want to obtain the list of numbers that does not match the phone_num i previously obtain using the SQL query above.

    I'm a bit confused of how to use the result i previously obtained ..and i'm not really sure whether this is possible or not.. is it possible to provide me with an example..

    Thanks a lot for your help

    Cheers

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: SQL Query

    Something like :

    SELECT T3.*
    FROM T3 WHERE T3.PHONE_NUM NOT IN (SELECT T1.phone_no
    FROM T1
    LEFT OUTER JOIN T2
    ON T1.case_id = T2.case_id )
    Shadow to Light

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    crespo, your subquery returns all T1 rows

    notice the original left outer join was ON T1.case_id = T2.case_id but then the WHERE clause was T2.case_id = T1.case_id which will eliminate the unmatched rows where T2.id is null

    that original left outer should really be an inner

    the final query can use a left outer (although the NOT EXISTS approach works too)

    Code:
    select T3.*
      from T3
    left outer
      join (
           select T2.id
             from T2
           inner
             join T1
               on T2.id = T1.id
           ) as X
        on T3.id = X.id
     where X.id is null
    rudy
    http://r937.com/

Posting Permissions

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