MS SQL RSS MS SQL RSS

Meeting at least n criteria from given criteria

Please support our MS SQL advertiser: Intel Parallel Studio Home
Reply
1 2

•
•
Join Date: Sep 2008
Posts: 5
Reputation: coolick81 is an unknown quantity at this point 
Solved Threads: 0
coolick81 coolick81 is offline Offline
Newbie Poster

Meeting at least n criteria from given criteria

 
0
  #1
Sep 9th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 2
Reputation: coderboi is an unknown quantity at this point 
Solved Threads: 0
coderboi coderboi is offline Offline
Newbie Poster

Re: Meeting at least n criteria from given criteria

 
0
  #2
Sep 9th, 2008
Try something like this...

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT DISTINCT(person) FROM TABLE WHERE ((height = 'tall' AND hair='brown') OR (height='tall' AND eyes='blue') OR (eyes='blue' AND hair = 'brown')
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 5
Reputation: coolick81 is an unknown quantity at this point 
Solved Threads: 0
coolick81 coolick81 is offline Offline
Newbie Poster

Re: Meeting at least n criteria from given criteria

 
0
  #3
Sep 9th, 2008
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....
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Meeting at least n criteria from given criteria

 
0
  #4
Sep 9th, 2008
you could also use something like this

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT person_id, hairmatch, colormatch, eyematch, sum(hairmatch, colormatch, eyematch) AS total FROM
  2. (SELECT person_id, 
  3.    (case when Hair = 'Tall' then 1 else 0 end) AS hairmatch,
  4. (case when HairColor = 'brown' then 1 else 0 end) AS colormatch,
  5. (case when EyeColor = 'blue' then 1 else 0 end) AS eyematch
  6. FROM
  7. person)
  8. GROUP BY person_id, hairmatch,colormatch,eyematch
  9.  
  10. -- add this to filter totals
  11. WHERE total >= 2
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 5
Reputation: coolick81 is an unknown quantity at this point 
Solved Threads: 0
coolick81 coolick81 is offline Offline
Newbie Poster

Re: Meeting at least n criteria from given criteria

 
0
  #5
Sep 10th, 2008
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) 
  1. SELECT name FROM (SELECT name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch,  classmatch + countrymatch + launchedmatch + typematch + displacementmatch + borematch + numgunsmatch as total,
  2. (CASE WHEN class = 'Kongo' THEN 1 ELSE 0 END) as classmatch,
  3. (CASE WHEN country = 'USA' THEN 1 ELSE 0 END) as countrymatch,
  4. (CASE WHEN launched = '1915' THEN 1 ELSE 0 END) as launchedmatch,
  5. (CASE WHEN type = 'bb' THEN 1 ELSE 0 END) as typematch,
  6. (CASE WHEN bore = '15' THEN 1 ELSE 0 END) as borematch,
  7. (CASE WHEN numguns = '8' THEN 1 ELSE 0 END) as numgunsmatch,
  8. (CASE WHEN displacement = '32000' THEN 1 ELSE 0 END) as displacementmatch
  9. 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
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Meeting at least n criteria from given criteria

 
0
  #6
Sep 10th, 2008
you need to use Sum around the fields you are adding

MS SQL Syntax (Toggle Plain Text) 
  1. 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) 
  1. GROUP BY
  2. 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
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Meeting at least n criteria from given criteria

 
0
  #7
Sep 10th, 2008
haven't tested this but this is closer to what you need

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch, 
  2. Sum(classmatch ,countrymatch , launchedmatch , typematch , displacementmatch , borematch , numgunsmatch) AS total
  3.  FROM
  4. -- subquery
  5. (SELECT name,
  6. 	(case when class = 'Kongo' then 1 else 0 end) AS classmatch,
  7. 	(case when country = 'USA' then 1 else 0 end) AS countrymatch,
  8. 	(case when launched = '1915' then 1 else 0 end) AS launchedmatch,
  9. 	(case when type = 'bb' then 1 else 0 end) AS typematch,
  10. 	(case when bore = '15' then 1 else 0 end) AS borematch,
  11. 	(case when numguns = '8' then 1 else 0 end) AS numgunsmatch,
  12. 	(case when displacement = '32000' then 1 else 0 end) AS displacementmatch
  13. FROM ships
  14. -- you need to do your joins on the tables here to pull class, country ... etc) 
  15. GROUP BY name, classmatch, borematch, displacementmatch, countrymatch, launchedmatch, typematch, numgunsmatch
  16. WHERE total>=4
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 5
Reputation: coolick81 is an unknown quantity at this point 
Solved Threads: 0
coolick81 coolick81 is offline Offline
Newbie Poster

Re: Meeting at least n criteria from given criteria

 
0
  #8
Sep 10th, 2008
hmm it tells me: The Sum function requires 1 argument(s). ?
Maybe you have to use '+' ??
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Meeting at least n criteria from given criteria

 
0
  #9
Sep 10th, 2008
hmmm, try it with the + then, make sure you use sum(a + b + c....)

works for me just using columns
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 5
Reputation: coolick81 is an unknown quantity at this point 
Solved Threads: 0
coolick81 coolick81 is offline Offline
Newbie Poster

Re: Meeting at least n criteria from given criteria

 
0
  #10
Sep 10th, 2008
•
•
•
•
Originally Posted by dickersonka View Post
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
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop maximum microsoft ms multithreading news number permission query result server sets sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC