Hi all,

Trying to identify whether a field value (from a single row) in one table is matched in a column (all rows) in table 2.

Only way I can think of is using something like:
((SUM(table1.fieldx = table2.fieldy))=1)

...but its (SQL Server) throwing a error msg (170).

How should I be writing this? Or is there a better way?

Thanks in advance.

Casey.

not real sure what you are asking, not sure why you have the sum in there, that will not work.

((SUM(table1.fieldx = table2.fieldy))=1)


write out what you are after, such as if x = y then sum the results from both fields, well then why do you have it equal to 1.

Just explain better so I can help.

Thanks for your reply mkkmg, I appreciate your time.

What I'm trying to achieve is a boolean test for a value in say 'table1 col1 row X' against 'multiple (all) rows in table 2 col1'.

eg.
***********
Table 1
Col1
3
***********
Table 2
Col1
2
4
8
5
9
3
2
***********

My thought was that I could you the sum function to return a value (0/1) based on whether there was a match, and use that in a where clause to exclude/include a given record.

Hope this illustrates my problem, if its not painfully obvious already, I don't have much experience with tsql! (I have an uncomfortable feeling I've overlooked an obvious way to solve this problem )

Thanks again for your time,

Cheers Casey.

THIS DOES NOT WORK, ERROR 170, INCORRECT SYNTAX
select (5=5)

BUT THIS MIGHT WORK
select (case 5 when 5 then 1 else 0 end)

IN YOUR QUERY
((SUM(CASE table1.fieldx WHEN table2.fieldy THEN 1 ELSE 0 END))=1)

Thankyou very much for your help cvandemaele, your exact response didn't work, apparently a aggregate function in a CASE statement isn't permitted; however in a HAVING clause it is!

FYI
--Performs a true/false test for 'make'
HAVING ((SUM(CASE tblStock.Make WHEN tblDealerMake.Make THEN 1 ELSE 0 END))=0)

Thanks again, I hope your day/evening is an enjoyable one!

Casey.