Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Load Table where <>

    Hi there.

    Trying to load new table based on the following and I am getting a nested loop join...

    Customer Table
    Canceled Table
    New Table

    I am declaring cursor as SELECT * from Customer t1, Canceled t2
    where Customer.id <> Canceled.id

    This is giving me a nested loop. Obviously my SQL skills are terrible. I can't figure out how to write the SQL to only load into the new table where the ID in Customer does not equal the ID Cancelled.

    Any help?
    Thanks all.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Maybe something like this:

    SELECT * from Customer t1 where not exists (select 1 from Canceled t2 where t1.id = t2.id);
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    Thanks

    Quote Originally Posted by Marcus_A View Post
    Maybe something like this:

    SELECT * from Customer t1 where not exists (select 1 from Canceled t2 where t1.id = t2.id);
    This worked. I've got to get my SQL skills up to speed. Sheesh.

    Thanks, Marcus.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by clemmdupree View Post
    This is giving me a nested loop.
    What's wrong with a nested loop, and how is that related to SQL skills?

    You could also use an outer join and select records that have no match.

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by n_i View Post
    What's wrong with a nested loop, and how is that related to SQL skills?

    You could also use an outer join and select records that have no match.
    The nested loop gave me a Cartesian product and I had to terminate the load. My SQL skills are terrible because I couldn't figure a way out of it.

Tags for this Thread

Posting Permissions

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