Non matching records

[rowan[canspam]]

I have two tables

ITEMS:
items,description,mfg

MANUFACTURER:
mfg,full_name

The items database has 230,000 records, the manufacturer, 493 records. I want
to see how many records in the items table don't have a matching record in the
manufacturer table.

If I
select count(*),manufacturer.full_name from items,manufacturer where
items.mfg=manufacturer.mfg group by mfg;

I end up with 493 lines showing total items per manufacturer. I know there are
items where the corresponding manufacturer table does not have a match. How do
I find which records in the items table don't match something in the
manufacturer table?

[Aggro]

rowan[canspam] wrote:

I have two tables

ITEMS:
items,description,mfg

MANUFACTURER:
mfg,full_name

The items database has 230,000 records, the manufacturer, 493 records.
I want to see how many records in the items table don't have a matching
record in the manufacturer table.

If I
select count(*),manufacturer.full_name from items,manufacturer where
items.mfg=manufacturer.mfg group by mfg;

This might be a little slow:

select count(*) from items
left join manufacturer on items.mfg=manufacturer.mfg
where manufacturer.mfg is null;

It works like this:
- Find a match for each row in table1
- If there is no match, mark table2 values with null
- Count only rows where table2 values are null ( the rows where we
didn't get a match)

[Aggro]

rowan[canspam] wrote:

I have two tables

ITEMS:
items,description,mfg

MANUFACTURER:
mfg,full_name

The items database has 230,000 records, the manufacturer, 493 records.
I want to see how many records in the items table don't have a matching
record in the manufacturer table.

If I
select count(*),manufacturer.full_name from items,manufacturer where
items.mfg=manufacturer.mfg group by mfg;

This might be a little slow:

select count(*) from items
left join manufacturer on items.mfg=manufacturer.mfg
where manufacturer.mfg is null;

It works like this:
- Find a match for each row in table1
- If there is no match, mark table2 values with null
- Count only rows where table2 values are null ( the rows where we
didn't get a match)