Meeting at least n criteria from given criteria

[coolick81]

Hi I wonder how to write a query where given record meets at list n criteria from given criteria?
For example i have a table PERSON like this:

Name
Height (short, medium, tall)
HairColor (brown, blonde, black)
EyesColor (blue, green, brown)

I want to fine people who meet at least two criteria from below:

blue eyes
brown hair
tall

How to do that??

Thanks

[coderboi]

Try something like this...

SELECT distinct(person) from table where ((height = 'tall' and hair='brown') or (height='tall' and eyes='blue') or (eyes='blue' and hair = 'brown')

[coolick81]

Ok it is right but what if i have 10 criteria and want a person to met at least 3 of them?
U can't just do it the same way as they are too many combinations....

[dickersonka]

you could also use something like this

select person_id, hairmatch, colormatch, eyematch, sum(hairmatch, colormatch, eyematch) as total from
(select person_id, 
   (case when Hair = 'Tall' then 1 else 0 end) as hairmatch,
(case when HairColor = 'brown' then 1 else 0 end) as colormatch,
(case when EyeColor = 'blue' then 1 else 0 end) as eyematch
from
person)
group by person_id, hairmatch,colormatch,eyematch

-- add this to filter totals
where total >= 2

[coolick81]

Hi, thank you for the hint.
I used it in my real example but still making some syntax errors:
the tables are:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)

I just need to get a name of a ship meeting at leat 4 criteria....

my query looks like:

select name from (select name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch,  classmatch + countrymatch + launchedmatch + typematch + displacementmatch + borematch + numgunsmatch as total,
(case when class = 'Kongo' then 1 else 0 end) as classmatch,
(case when country = 'USA' then 1 else 0 end) as countrymatch,
(case when launched = '1915' then 1 else 0 end) as launchedmatch,
(case when type = 'bb' then 1 else 0 end) as typematch,
(case when bore = '15' then 1 else 0 end) as borematch,
(case when numguns = '8' then 1 else 0 end) as numgunsmatch,
(case when displacement = '32000' then 1 else 0 end) as displacementmatch
from ships, classes where ships.name=classes.class and total>=4)

Do you know what is wrong??

thanks

[dickersonka]

you need to use Sum around the fields you are adding

Sum(classmatch,countrymatch, launchmatch......etc) as total

you also need to use the group by because sum is an aggregate

group by
name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch

follow the example and take it from there, your cases are correct, are you sure you need

i'll post your example

[dickersonka]

haven't tested this but this is closer to what you need

select name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch, 
Sum(classmatch ,countrymatch , launchedmatch , typematch , displacementmatch , borematch , numgunsmatch) as total
 from
-- subquery
(select name,
	(case when class = 'Kongo' then 1 else 0 end) as classmatch,
	(case when country = 'USA' then 1 else 0 end) as countrymatch,
	(case when launched = '1915' then 1 else 0 end) as launchedmatch,
	(case when type = 'bb' then 1 else 0 end) as typematch,
	(case when bore = '15' then 1 else 0 end) as borematch,
	(case when numguns = '8' then 1 else 0 end) as numgunsmatch,
	(case when displacement = '32000' then 1 else 0 end) as displacementmatch
from ships
-- you need to do your joins on the tables here to pull class, country ... etc) 
group by name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch
where total>=4

[coolick81]

hmm it tells me: The Sum function requires 1 argument(s). ?
Maybe you have to use '+' ??

[dickersonka]

hmmm, try it with the + then, make sure you use sum(a + b + c....)

works for me just using columns

[coolick81]

hmmm, try it with the + then, make sure you use sum(a + b + c....)

works for me just using columns

ok, i got this:

select name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch, 
Sum(classmatch + countrymatch + launchedmatch + typematch + displacementmatch + borematch + numgunsmatch) as total
 from
(select name,
	(case when class = 'Kongo' then 1 else 0 end) as classmatch,
	(case when country = 'USA' then 1 else 0 end) as countrymatch,
	(case when launched = '1915' then 1 else 0 end) as launchedmatch,
	(case when type = 'bb' then 1 else 0 end) as typematch,
	(case when bore = '15' then 1 else 0 end) as borematch,
	(case when numguns = '8' then 1 else 0 end) as numgunsmatch,
	(case when displacement = '32000' then 1 else 0 end) as displacementmatch
from ships where classes.class=ships.class and total>=4
group by name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch)

still i get "Incorrect syntax near ')' "
if you want to try it or try other exercises: http://www.sql-ex.ru/
number 32

thanks for help

[dickersonka]

once again you need to group by using the outer query

from ships where classes.class=ships.class)
-- note the end parenthese
-- now put your group by and total
group by name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch
where total>=4