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
)

Query

Hi!

I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3

table 1

id	Col1	Col2	maxValues
1	aa	bb	10
2	cc	dd	10
3	ee	ff	10
4	gg	hh	10
5	jj	kk	10

table 2

id	Col1	Col2	CurrentValue
1	cc	dd	7
2	gg	hh	3
3	jj	kk	5

table 3

id	Col1	Col2	AvailableEntries
1	aa	bb	10
2	cc	dd	7
3	ee	ff	10
4	gg	hh	3
5	jj	kk	5

Thanks for your help.

If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:
select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you

query

I have 2 database:
first A
second B
in B there is a table X
how can i save a view in A that select the table X in B?
ThanksYes, but not in the query designer. Write TSQL code instead:
CREATE VIEW ... AS
SELECT ...
FROM otherdb.dbo.tblname
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"killer" <matteo.milano@.katamail.com> wrote in message
news:_u5tb.114858$vO5.4451339@.twister1.libero.it...
> I have 2 database:
> first A
> second B
> in B there is a table X
> how can i save a view in A that select the table X in B?
>
> Thanks
>