qry that returns rec count recs not in other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

qry that returns rec count recs not in other table

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2002-07-25 : 15:22:22

I have 2 tables. The first table is a master table with 3 fields: record id, list, and value. The second table is a lookup table that has the list and value in it, as well as fieldname. I need a query that will return a count of records in the master table that do not exist in the lookup table based on list and value. It seems straightforward but my brain doesn't seem to be working. I keep returning the count of records that don't match in the lookup table, instead of the master table. can anyone help? This was my code that isn't working:

select m.list, m.value, count(*)
from master m, lookup l
where m.list = l.list and
m.value <> l.value
and fieldname = 'BOC'
group by m.list, m.value

Thanks in advance.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-25 : 15:27:24


select count(*)
from [master] m
where not exists (
    select 1
    from [lookup]
    where m.list = list and
          m.value <> value and
          fieldname = 'BOC')

This is a shot in the dark that may get you started. I think a correlated subquery is the way to go. However, with the DDL I am a bit confused on how the tables are related and you fieldname column has really got me scratching my head.

<O>

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2002-07-25 : 15:52:17

Thanks, I tried what you suggested but i am looking for a count of records, from the master, per fieldname and per value that is not found in the lookup tables. In this case the fieldname in the looku table = 'BOC'

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-25 : 16:15:20

Post your ddl, some sample data and your expected rowset....

<O>

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-25 : 19:39:04

Try this...

select count(*)
from master
where list+value NOT IN (SELECT list+value from lookup where fieldname = 'BOC' )

or this if value is numeric

select count(*)
from master
where list+CAST(value AS varchar(100)) NOT IN (SELECT list+CAST(value AS varchar(100)) from lookup where fieldname = 'BOC')

Garth
www.SQLBook.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources