SQL Query Using UNION/EXISTS/INTERSECT To Gather Common Data on Same Tables

Hi,
I've attempted a few possible ways to write this query, but I'm either missing something simple, or it's more complicated than my current SQL skills can handle. In a nutshell the user has an expanded search page where they can select various criteria. I dynamically build the where clause portion of the query to produce the results based on their selections. There are 2 tables (one to many relationship). If they only make one selection the query looks like snippet 1, 2 selections looks like snippet 2, etc. Using the "AND" operator in snippet 2 produces zero results because as you'll see the same column is being referenced for the passed data. Using the "OR" brings back everything, but I want what is common among all of the user's selection.

"UNION" doesn't work (snippet 3) because it brings back everything for each query and I would like what is common among each. I tried using "INTERSECT", but as I understand it it is only functional in the Analysis Services feature of SQL Server (correct me if I'm wrong on this). At least setting it up provoked a syntax error in SQL Server Management Studio. I also tried employing "EXISTS", but I seem to get the same results as with "UNION" when working with multiple selection choices (snippet 4).

Anyway, could someone point me in the right direction? The key element here is that what would traditonal an easy situation is complicated by the fact that I'm working with fields that are not set up as columns but are instead dynamic rows of data (Book2.xls, which is produced by the UNION query. I highlighted the records that I'd like to come back since they contain both selection choices by the user). "Study_Template_Item" would be considered the column if it were a "hard coded" field.

Cheers,
Ty

PS: I'm using SQL Server 2008

snippet 1

SELECT     
PreclinicalStudyMaster.Preclinical_Study_Master_ID, PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
PreclinicalStudyItems.Study_Template_Item, PreclinicalStudyItems.Study_Template_Item_Value
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE     
(PreclinicalStudyItems.Study_Template_Item = 'Device Comments') AND 
(PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%') 

UNION

SELECT     
PreclinicalStudyMaster.Preclinical_Study_Master_ID, PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
PreclinicalStudyItems.Study_Template_Item, PreclinicalStudyItems.Study_Template_Item_Value
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE
(PreclinicalStudyItems.Study_Template_Item = 'Director' AND 
PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

ORDER BY PreclinicalStudyMaster.Preclinical_Study_Master_ID

Select allOpen in new window

snippet 2

SELECT     
 PreclinicalStudyMaster.Preclinical_Study_Master_ID, 
 PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
 PreclinicalStudyItems.Study_Template_Item, 
 PreclinicalStudyItems.Study_Template_Item_Value
FROM
 PreclinicalStudyItems INNER JOIN
 PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE     
(PreclinicalStudyItems.Study_Template_Item = 'Device  Comments' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
AND
(PreclinicalStudyItems.Study_Template_Item = Director' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

Select allOpen in new window

snippet 4

SELECT     
 PreclinicalStudyMaster.Preclinical_Study_Master_ID, 
 PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
 PreclinicalStudyItems.Study_Template_Item, 
 PreclinicalStudyItems.Study_Template_Item_Value
FROM
 PreclinicalStudyItems INNER JOIN
 PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE     
 (PreclinicalStudyItems.Study_Template_Item = 'Device  Comments' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')

Select allOpen in new window

snippet 3

SELECT     
a.Preclinical_Study_Master_ID, a.Preclinical_Study_Master_Code_ID
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster AS a ON 
                      PreclinicalStudyItems.Preclinical_Study_Master_ID = a.Preclinical_Study_Master_ID 
WHERE EXISTS
(
	SELECT * FROM PreclinicalStudyItems AS b
	WHERE a.Preclinical_Study_Master_ID = b.Preclinical_Study_Master_ID AND
	(PreclinicalStudyItems.Study_Template_Item = 'Device Comments' AND 
	PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
)

AND
	EXISTS
	(
	SELECT * FROM PreclinicalStudyItems AS c
	WHERE a.Preclinical_Study_Master_ID = c.Preclinical_Study_Master_ID AND
	(PreclinicalStudyItems.Study_Template_Item = 'Director' AND 
	PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%') 
	)

Select allOpen in new window

Book2.xls