select any records that have values in at least two of four cols

[hitecbill]

ok products db...for printers/copiers...

fields are print, copy, scan, fax...they will be either 1 or 0...

what would a query be to return any records that contain at least two of these columns?


i'm thinking this, but it doesn't seem too elegant?

select * from products where
(copy=1 and (print=1 or scan=1 or fax=1)) or
(print=1 and (copy=1 or scan=1 or fax=1)) or
(scan=1 and (copy=1 or print=1 or fax=1)) or
(fax=1 and (copy=1 or print=1 or scan=1))

[aussie girl]

I would add another column named quantity and have the value in there...
then SELECT * from products WHERE quantity = 1

[chazzy]

ok products db...for printers/copiers...

fields are print, copy, scan, fax...they will be either 1 or 0...

what would a query be to return any records that contain at least two of these columns?


i'm thinking this, but it doesn't seem too elegant?

select * from products where
(copy=1 and (print=1 or scan=1 or fax=1)) or
(print=1 and (copy=1 or scan=1 or fax=1)) or
(scan=1 and (copy=1 or print=1 or fax=1)) or
(fax=1 and (copy=1 or print=1 or scan=1))

Assuming that the only possible values are 0/1 for all of these columns, you can do this query:


SELECT * FROM products WHERE copy + print + scan + fax > 1;


Should work in most databases.

[hitecbill]

yes, chazzy, that's the clever one :)

a mate sent me this:

Where (print+copy+scan+fax) >= 2

same idea!


thx for responses :)