There are a couple of ways to do what you want:

SELECT ItemID FROM Table1 WHERE ItemID NOT IN (SELECT ItemID FROM Table 2);

will do it, as will:

SELECT Table1.ItemID FROM Table1 LEFT JOIN Table 2 ON
Table1.ItemID=Table2.ItemID WHERE Table2.Itemid IS NULL;

The outer join is probably marginally faster, depending on the distribution
of values for ItemID, the relative sizes of the 2 tables, and the presence
of indexes on the relevant columns.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Bonnie James [mailto:wytmagic@h...]
Sent: Monday, July 29, 2002 1:22 AM
To: sql language
Subject: [sql_language] Getting not equal results in a select query


I have two tables:

Table1             Table2
ItemID             ItemID
1                  1
2                  2
3
4
5

What I am trying to do is return columns from Table 1 That ARE NOT listed
in Table 2. For example, the result of the select query for this would be:

ItemID
3
4
5

I am lost. I keep getting a blank set with:

SELECT     Table1.ItemID
FROM         Table1 INNER JOIN
                      Table2 ON Table1.ItemID = Table2.ItemID
WHERE     (NOT (Table1.ItemID = Table2.ItemID))

I have tried the different joins with no luck. What am I doing wrong?

Thanks!!