Hello Everyone and thanks for your help in advance. I am new to building queries that join tables and am in need of assistance. I have two tables:

tblIDX
tblIDXTemp

They are identical tables with different data. There is a key field MLS_LISTING_ID. I want to find all record in tblIDXTemp that are not in tblIDX. I have tried several combinations, but can't seem to get the right syntax. Any help would be greatly appreciated. Thanks.

SELECT dbo.tblIDXTemp.MLS_LISTING_ID
FROM dbo.tblIDX RIGHT OUTER JOIN
                      dbo.tblIDXTemp ON dbo.tblIDX.MLS_Listing_ID = dbo.tblIDXTemp.MLS_LISTING_ID
WHERE (dbo.tblIDX.MLS_Listing_ID IS NULL)

Basically if the Id on the Temp side is null and not in tblIDX.

Sal

Thanks for the help Sal. However, the situation is that an MLS_LISTING_ID could be in the tblIDXTemp that is not in the tblIDX. These are the records I am trying to query.

...and Sal's query will find them for you.

You need to study up on how OUTER JOINs work.

You can think of the result of a JOIN operation to be an intermediate table. The rows of this intermediate table contain the rows of both tables which satisfy the JOIN condition in the ON clause. The columns of this intermediate table are all the columns of both tables. These columns are given the name <tablename.columnname>. When you specify a WHERE clause (or reference the columns in any clause, i.e. SELECT, GROUP BY, etc), the columns in the predicates of that clause refer to the columns of this intermediate table and not to the columns in the underlying tables. This last point is extremely important.

For INNER JOINS, the rows included in the intermediate table are only those rows which "match" in both tables. Again, the columns of this intermediate table are the columns from both tables. In the case of an OUTER JOIN, one of the tables is called the preserved table. All of the rows in the preserved table are copied into the intermediate table. All columns from those rows from the "other" (or unpreserved) table which match the rows of the intermediate are copied into the intermediate table in the same manner as an INNER JOIN. But, those rows of the preserved table which have no match in the unpreserved table have the columns corresponding to the columns of the unpreserved table set to null in the intermediate table.

Which table is considered the preserved table depends on the "handedness" of the OUTER JOIN. A LEFT OUTER JOIN preserves the table on the left side of the JOIN operator; a RIGHT OUTER JOIN preserves the table on the right side.

In Sal's, solution, the preserved table is tblIDXTemp. So, all rows of this table will be present in the intermediate table, and those rows which have no match in tblIDX will have the corresponding columns of tblIDX in the intermediate table set to NULL. The WHERE clause then selects those rows.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I think Sal and Jeff are right, but maybe this syntax is what you are looking for(?):

SELECT * FROM tblIDXTemp WHERE NOT EXISTS (SELECT tblIDX.MLS_LISTING_ID FROM tblIDX WHERE tblIDX.MLS_LISTING_ID = tblIDXTemp.MLS_LISTING_ID)

It's very likely that the correlated subquery will be slower than the OUTER JOIN (depending on the indexes and how smart the query processor is), but that is another way to do it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com