Select multiple records with AND? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select multiple records with AND?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-08 : 12:00:00

David writes "I built a search tool that allows users to create a compound query based on criteria they choose. The database contains skills and will be used for project resource allocation. The most simple query a user could request could be:

SELECT * FROM get_emp_resource_skills_v WHERE (SkillCategoryID= 14 AND SkillID= 'dboracle' AND SkillRating >= 3)

where they are asking to see all employees skilled in Oracle with a rating of at least 3. Another query could be:

SELECT * FROM get_emp_resource_skills_v WHERE (SkillCategoryID= 14 AND SkillID= 'dboracle' AND SkillRating >= 3) UNION SELECT * FROM get_emp_resource_skills_v WHERE (SkillCategoryID= 14 AND SkillID= 'dbsql' AND SkillRating >= 4)

where they are asking to see all employees skilled in Oracle with a rating of at least 3 AND all employees skilled in SQL with a rating of at least 4. The above query does not work, as the UNION returns people skilled in Oracle or in SQL, but does not limit the results to only those skilled in BOTH.

The above queries are dynamically generated by ASP. What should the syntax be in order to produce the results I am looking for? Bear in mind, these queries could be very long - the above example only requested 2 seperate skills however there could be many more.

Originally, I tried using a query like:

SELECT * FROM get_emp_resource_skills_v WHERE ((SkillCategoryID= 14 AND SkillID= 'dboracle' AND SkillRating >= 3) AND (SkillCategoryID= 14 AND SkillID= 'dbsql' AND SkillRating >= 4))

but that syntax would not return the proper results.

Any help is appreciated.

Thanks!"

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-08 : 12:31:18

Do they need to be able to specify skillRating / SkillCategory per SkillID? You may need to move SkillCategory inside the OR statement, but this should get you going.

declare @get_emp_resource_skills_v table (empID int, SkillCategoryID int, skillID varchar(10), skillRating int)
insert into @get_emp_resource_skills_v
	select 1, 14, 'dboracle', 2 union
	select 2, 14, 'dboracle', 3 union
	select 3, 14, 'dboracle', 4 union
	select 4, 14, 'dbsql', 3 union
	select 5, 14, 'dbsql', 4 union
	select 6, 14, 'dbsql', 5


SELECT empID, SkillCategoryID, skillID, skillRating 
FROM @get_emp_resource_skills_v 
WHERE SkillCategoryID = 14 
	AND (SkillID= 'dboracle' AND SkillRating >= 3) OR (SkillID= 'dbsql' AND SkillRating >= 4)

Nathan Skerl

vwood
Starting Member

2 Posts

Posted - 2005-08-08 : 12:52:21

Just off the top of my head, I'm thinking an inner join would work well.

SELECT *
FROM (get_emp_resource_skills_v AS tbl1
INNER JOIN get_emp_resource_skills_v AS tbl2
ON tbl1.EmployeeID = tbl2.EmployeeID)
WHERE (tbl1.SkillCategoryID= 14 AND tbl1.SkillID= 'dboracle' AND tbl1.SkillRating >= 3)
AND (tbl2.SkillCategoryID= 14 AND tbl2.SkillID= 'dbsql' AND tbl2.SkillRating >= 4))
;

Admittedly, this could get cumbersome as the user adds multiple criterion, especially the nested parenthesis, but I think this approach should do the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources