Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Unexpected results from a simple query, totally bizzarre...

    I am currently working on creating a simple catalog keycode analysis application for work. I am in the process of writing all of the necessary SQL queries and I ran into a strange problem. I have 4 keycode reference tables which contain a list of unique keycodes and any relevant data about them. I then have one table that contains order information for the past 3 years and another table with all of our customer data. I have written a query that should look at the 2006 keycode table and compare it to all of the unique keycodes that are attached to orders and return all of the keycodes that don't appear in the order table. Instead I am presented with a list of all 212 of the keycodes that are in the keycode table. This is impossible because if I remove the "not" in the "not in (table1.Keycode)" statement 125 records are returned. I have run this query using "<>" & "not like" each time it returns the same results. See Query below:

    SELECT DISTINCT 2006_Keycode.keycode, 2006_Keycode.Drop, 2006_Keycode.Catalog,2006_Keycode.Cost, 2006_Keycode.Segment, 2006_Keycode.Qty, 2006_Keycode.Qty_Total_Per_Mailing
    FROM 2006_Keycode,
    (SELECT DISTINCT Orders_36Mo.Keycode FROM Orders_36Mo) AS table1
    WHERE 2006_Keycode.Keycode not in (table1.Keycode);


    If I modify the query to the following way and got no results:

    SELECT DISTINCT 2006_Keycode.keycode, 2006_Keycode.Drop, 2006_Keycode.Catalog,2006_Keycode.Cost, 2006_Keycode.Segment, 2006_Keycode.Qty, 2006_Keycode.Qty_Total_Per_Mailing
    FROM 2006_Keycode
    WHERE 2006_Keycode.Keycode in (SELECT DISTINCT Orders_36Mo.Keycode FROM Orders_36Mo);


    When I ran:

    select 2006_keycode.keycode
    from 2006_keycode, orders_36mon_keycode;


    65,084 records were returned. This is very odd because when I selected "SELECT DISTINCT Orders_36Mo.Keycode FROM Orders_36Mo" into a table it only returned 307 records. It seems like there is some kind of crazy loop that is being created and I can't figure out why. BTW: I also tried the exact same queries with the exact same data in Access with the exact same results. My co-worker and I looked over the queries for at least an hour and couldn't figure this one out, maybe we're just blind, but I hope someone here can help me. Thanks. -Skip

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    you need to have a JOIN condition so that the tables are joined on one column from each column. without it you will get a cross join on your tables. a cross join returns every row in the second table for every row in the first table. If you have 40 rows in one table and 30 in the other, then without a join condition you will end up with 1,200 rows in your result.

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    The join does work for the last query in question (thanks guelphdad,) but what about the first two? Those queries are the real problem. I even tried using JOIN (cross, left, inner, etc) instead of where and again got the same results. Does anyone have any idea where I am going wrong with this? If I am not explaining the problem well, please let me know.

    SELECT DISTINCT t1.keycode, t1.Drop, t1.Catalog, t1.Cost,t1.Segment, t1.Qty, t1.Qty_Total_Per_Mailing
    FROM 2006_Keycode AS t1 CROSS JOIN Orders_36Mo AS t2 ON t1.keycode not in (t2.Keycode);

    212 rows returned.
    Thanks.
    Last edited by skiptabor; 02-20-07 at 10:51.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ...look at the 2006 keycode table and compare it to all of the unique keycodes that are attached to orders and return all of the keycodes that don't appear in the order table.
    Code:
    SELECT 2006_Keycode.keycode
         , 2006_Keycode.Drop
         , 2006_Keycode.Catalog
         , 2006_Keycode.Cost
         , 2006_Keycode.Segment
         , 2006_Keycode.Qty
         , 2006_Keycode.Qty_Total_Per_Mailing
      FROM 2006_Keycode
    LEFT OUTER
      JOIN (
           SELECT DISTINCT Orders_36Mo.Keycode 
             FROM Orders_36Mo
           ) AS table1
        ON table1.Keycode = 2006_Keycode.Keycode 
     WHERE table1.Keycode IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    That's It! Thanks, r937. If you have the time, could you explain why the LEFT OUTER JOIN is required. I am pretty sure that I have run queries before to do similar operations using only a WHERE clause, without any JOINs. I could be wrong, but guelphdad's explanation does not seem to apply completely to your solution (query.) Thanks again to all for the help.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    LEFT OUTER JOIN is one of the fundamental building blocks of SQL queries

    basically, it says to return all the rows of the left table

    then, the IS NULL test will keep only those which don't have a matching row in the right table

    which is what you wanted, right?

    guelphdad's explanation applies perfectly to my query, because i do have an ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2004
    Posts
    4
    Ok, I get. Thanks for the explanation.

Posting Permissions

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