Ad:

MS SQL Discussion Thread
Unsolved
Views: 2122

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Sep 9th, 2008

Meeting at least n criteria from given criteria

Expand Post »

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

Similar Threads

Variable scope problem in C++
new to PHP and need help in PHP
What is ethical SEO? in Search Engine Optimization

coolick81

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Sep 2008

Permalink

Sep 9th, 2008

Re: Meeting at least n criteria from given criteria

Try something like this...

MS SQL Syntax (Toggle Plain Text)
SELECT DISTINCT(person) FROM TABLE WHERE ((height = 'tall' AND hair='brown') OR (height='tall' AND eyes='blue') OR (eyes='blue' AND hair = 'brown')
SELECT distinct(person) from table where ((height = 'tall' and hair='brown') or (height='tall' and eyes='blue') or (eyes='blue' and hair = 'brown')

coderboi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Apr 2008

Permalink

Sep 9th, 2008

Re: Meeting at least n criteria from given criteria

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....

coolick81

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Sep 2008

Permalink

Sep 9th, 2008

Re: Meeting at least n criteria from given criteria

you could also use something like this

MS SQL Syntax (Toggle Plain Text)
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
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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 10th, 2008

Re: Meeting at least n criteria from given criteria

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:

sql Syntax (Toggle Plain Text)
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)
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

Last edited by peter_budo; Sep 11th, 2008 at 5:36 am. Reason: Keep It Organized - please use [code] tags

coolick81

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Sep 2008

Permalink

Sep 10th, 2008

Re: Meeting at least n criteria from given criteria

you need to use Sum around the fields you are adding

MS SQL Syntax (Toggle Plain Text)
Sum(classmatch,countrymatch, launchmatch......etc) AS total
Sum(classmatch,countrymatch, launchmatch......etc) as total

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

MS SQL Syntax (Toggle Plain Text)

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

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 10th, 2008

Re: Meeting at least n criteria from given criteria

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

MS SQL Syntax (Toggle Plain Text)
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
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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 10th, 2008

Re: Meeting at least n criteria from given criteria

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

coolick81

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Sep 2008

Permalink

Sep 10th, 2008

Re: Meeting at least n criteria from given criteria

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

works for me just using columns

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 10th, 2008

Re: Meeting at least n criteria from given criteria

Click to Expand / Collapse Quote originally posted by dickersonka ...

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

works for me just using columns

ok, i got this:

(Toggle Plain Text)

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

coolick81

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Sep 2008

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Installing SQL Server Express on Vista Home Premium

Next Thread in MS SQL Forum Timeline: Upgrade SQL 2005 Standard and SQL 2005 Enterprise Edition

DaniWeb Message

Cancel Changes

User Name
Password