weird query requirement

[Steve]

I have 2 related tables;

[Tbl_BaseItems]
BaseItemID PK
ItemID FK

[Tbl_Items]
ItemID PK


I need a query that will get all the Item that AREN'T in Tbl_BaseItems.
Something like;
SELECT * FROM Tbl_Items
WHERE Tbl_Items.ItemID <isn't in> Tbl_BaseItems.ItemID

Is that possible? I can't think of anything....

Thanks!
Steve

 

[Wayne Morgan]

Go to the database window, Queries tab. Click on the New button, not the New
Query... string in the window itself. Choose the Find Unmatched Query
Wizard.

 

[Steve]

I thought this might do it:
SELECT DISTINCT Tbl_Items.ItemID
FROM Tbl_Items, Tbl_BaseItems
WHERE Tbl_Items.ItemID <> Tbl_BaseItems.ItemID;

This still returns all the records though

If I change WHERE Tbl_Items.ItemID <> Tbl_BaseItems.ItemID;
to
WHERE Tbl_Items.ItemID = Tbl_BaseItems.ItemID;

it only returns the ones that ARE in Tbl_BaseItems

it seems like the <> operator is having no effect....?

 

[Denis Dougall]

I would suggest you do what Wayne said, it's a better way to learn. If you
want to use indexes you could use something like :

select A.*
from Table1 A
where not exist (select 1 from Table2 B where B.col1=A.col1
and B.col2 = A.col2)


HTH,

Denis

 

[John Vinson]

I thought this might do it:
SELECT DISTINCT Tbl_Items.ItemID
FROM Tbl_Items, Tbl_BaseItems
WHERE Tbl_Items.ItemID <> Tbl_BaseItems.ItemID;

This still returns all the records though

If I change WHERE Tbl_Items.ItemID <> Tbl_BaseItems.ItemID;
to
WHERE Tbl_Items.ItemID = Tbl_BaseItems.ItemID;

it only returns the ones that ARE in Tbl_BaseItems

it seems like the <> operator is having no effect....?

It's working precisely as it should.

Let's say you have ItemIDs 35 and 45 in both tables.

When you create this "Cartesian Join" query, with no join line, Access
will pair every single record in tbl_Items with every record in
tbl_BaseItems - that is, if you have 1000 records in tbl_Items, and
2000 in tbl_BaseItems, you'll get all 2,000,000 possible combinations.

When you look at the combined record comparing tbl_Items ItemID 35
with tbl_BaseItems ItemID 35 the record will be correctly rejected -
but down that long list you'll be comparing tbl-Items 35 with
tbl_BaseItems 45; your inequality criterion will be true, so you'll
see Item 35.

The correct SQL is a "frustrated outer join":

SELECT tbl_Items.ItemID
FROM tbl_Items LEFT JOIN tblBase_Items
ON tblBase_Items.ItemID = tbl_Items.ItemID
WHERE tblBase_Items.ItemID IS NULL;

This works by finding all records in tbl_Items; joining each with its
corresponding record in tblBase_Items, if there is one; and excluding
all those records which DO have a match. The ones which don't will
have a NULL returned for tblBase_Items.ItemID.

John W. Vinson[MVP]

 

[Steve]

It's working precisely as it should.

Let's say you have ItemIDs 35 and 45 in both tables.

When you create this "Cartesian Join" query, with no join line, Access
will pair every single record in tbl_Items with every record in
tbl_BaseItems - that is, if you have 1000 records in tbl_Items, and
2000 in tbl_BaseItems, you'll get all 2,000,000 possible combinations.

When you look at the combined record comparing tbl_Items ItemID 35
with tbl_BaseItems ItemID 35 the record will be correctly rejected -
but down that long list you'll be comparing tbl-Items 35 with
tbl_BaseItems 45; your inequality criterion will be true, so you'll
see Item 35.

The correct SQL is a "frustrated outer join":

SELECT tbl_Items.ItemID
FROM tbl_Items LEFT JOIN tblBase_Items
ON tblBase_Items.ItemID = tbl_Items.ItemID
WHERE tblBase_Items.ItemID IS NULL;

This works by finding all records in tbl_Items; joining each with its
corresponding record in tblBase_Items, if there is one; and excluding
all those records which DO have a match. The ones which don't will
have a NULL returned for tblBase_Items.ItemID.

John W. Vinson[MVP]

Awesome response, John, thanks for taking the time to explain it. :0)
Have a good weekend!
Steve