query result that depends on the count of matching field contents in one record

[Ivan Smahin]

Hello Ralf,

Saturday, May 7, 2005, 1:28:10 AM, you wrote:

RS> Hi,

RS> I want to create a search where I get a result that depends on the
RS> count matching field contents in one record. But I can't get it. May
RS> be, I am thinking in the wrong direction.
RS> Lets assume, I have a table with 4 fields (vshort). Now I want to get
RS> all records where more than 2 field matches a condition.

RS> Example:

RS> if the code "field1 Between 1 and 5" would return 0 or 1, I could add
RS> all results:

RS>   SELECT * FROM TABLE1  WHERE (  (  field1=2 ) + (  field2=7 ) + (
RS> field3=10 )  + (  field4=1 )   ) >2

RS> or

RS>   SELECT * FROM TABLE1  WHERE (  (  field1 BETWEEN 1 AND 5 ) + (  field2
RS> BETWEEN 1 AND 7 ) + (  field3 BETWEEN 2 AND 4 )  + (  field4 BETWEEN 1
AND 2 )   ) >>2

RS> Any solution for that?


RS> Thanks
RS> Ralf


RS> _______________________________________________
RS> Valentina mailing list
RS> Valentina at lists.macserve.net
RS> http://lists.macserve.net/mailman/listinfo/valentina

Try it:

...
WHERE
     field1 = 2
     AND field2 = 7
     AND field3 = 10
     AND field4 = 1

     AND (field1 + field2 + field3 + field4) > 2

-- 
Best regards,
 Ivan                            mailto:IvanSmahin at public.kherson.ua