Wednesday, March 28, 2012

query (deals with exclusions)

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.If the columns LIST and VALUE are the joining columns between the MASTER table and the LOOKUP table then try this query:

SELECT COUNT(*)
FROM MASTER m
WHERE NOT EXISTS
(
SELECT *
FROM LOOKUP l
WHERE l.LIST = m.LIST
AND l.VALUE = m.VALUE
)

No comments:

Post a Comment