By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 985 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Non matching records

P: n/a
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?

Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Jul 19 '05 #2

P: n/a
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)
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.