WHERE return matches for multiple values

Transact-SQL
1

Eveming all,

I was given this little problem by a colleague and I've been mulling it over for a few days with little success.

Apologies in advance for the bad explanation... I have a table containing employee types, when I query the table I want to return multiple types. In the example below, if I query Emp_Type for 'Teacher Primary' then I'd like to return matches for :
'Teacher Primary'
'Teacher Primary & Secondary'
'Instructor Primary'
'Instructor Primary & Secondary'

I'm not a huge fan of CASE statements and in all honesty if I wrote out every eventuality it would be long and ugly.

Is there a better way of achieving this that I'm over looking...?

Thanks

Dave

CREATE TABLE #TempEmployee
(
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(20),
Emp_Type VARCHAR(40)
)

INSERT INTO #TempEmployee VALUES 
(1, 'Bob', 'Teacher Primary'),
(2, 'Danny', 'Teacher Secondary'),
(3, 'Steve', 'Teacher Primary & Secondary'),
(4, 'Ruby', 'Instructor Primary'),
(5, 'Lisa', 'Instructor Secondary'),
(6, 'Chris', 'Instructor Primary & Secondary'),
(7, 'Robby', 'Teaching Assistant'),
(8, 'Anne', 'Nursery Assistant'),
(9, 'Matt', 'Nursery Nurse'),
(10, 'Jenny', 'Nursery Nurse / TA')

Declare @Wanted_Type Varchar(20)
SET @Wanted_Type = 'Teacher Primary'

SELECT Emp_Name, Emp_Type
FROM #TempEmployee
WHERE Emp_Type LIKE
CASE
	WHEN @Wanted_Type = 'Teacher Primary' THEN
	'Teacher%' + '%Primary%'
	WHEN @Wanted_Type = 'Teacher Secondary' THEN
	'Teacher%' + '%Secondary%'
	WHEN @Wanted_Type = 'Teacher Primary & Secondary' THEN
	'Teacher%' + '%ary%'
	WHEN @Wanted_Type = 'Nursery Nurse / TA' THEN
	'Nursery Nurse'
END

DROP TABLE #TempEmployee
2

First I would create a lookup table with the types and a lookup table for "super-types" to further group the types.
So Employee would have a foreign key to LookupEmployeeType which would have a foreign key to LookupEmployeeSuperType.

1 Like
3

Hi

Does this help you ?

Please let me know

4

Morning,

thanks for the replies. I'm sort of leaning towards using a lookup table because it should make the query a bit tidier and secondly, because it'll make future ammendments easier..!

I assumed I could do this :

SELECT Emp_Name, Emp_Type
FROM #TempEmployee
WHERE Emp_Type IN 
(
	CASE
		WHEN @Wanted_Type = 'Teacher Primary' THEN
		(SELECT Lookup_TeacherPrimary FROM #TempLookup)
	END
)

but on execution I'm getting a 'Subquery returned more than 1 value' error and I'm not sure why...

EDIT:

Suddenly realised I was going about this the wrong way, change the layout of the lookup table and I can simply do it this way

SELECT Emp_Name, Emp_Type
FROM #TempEmployee
WHERE Emp_Type IN 
(
	SELECT Lookup_Type 
	FROM #TempLookup
	WHERE Lookup_Wanted = @Wanted_Type
)
5

...and another way is

SELECT Emp_Name, Emp_Type
FROM #TempEmployee te
WHERE EXISTS 
(
	SELECT 1
	FROM #TempLookup tl
	WHERE te.Emp_Type = tl.Lookup_Type 
             AND  Lookup_Wanted = @Wanted_Type
)
6

Hey,

I've implemented a lookup table and its working perfectly, thanks for your help.