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

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.

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.

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.

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

Ok, I get. Thanks for the explanation.