quote:

---

Originally posted by dmoonme

Sample data

SURVEYID,FIELDID,VALUE1,VALUE2,VALUE3
20,5,Mentor,None,NULL
20,246,Fluent,Fluent,Fluent
21,5,None,None,NULL
21,246,Limited,Limited,Limited

The select statement that's not working, no records are returned.

SELECT dbo.SurveyDetails.FIELDID, dbo.SurveyDetails.VALUE1, dbo.SurveyDetails.VALUE2, dbo.SurveyDetails.VALUE3 FROM dbo.SurveyDetails WHERE (dbo.SurveyDetails.FIELDID=246) AND(dbo.SurveyDetails.FIELDID=5 AND (VALUE1 LIKE '%mentor%' OR VALUE2 LIKE '%mentor%'))

---

quote:

---

Originally posted by dmoonme

The desired result would be

20,5,Mentor,None,NULL
20,246,Fluent,Fluent,Fluent

Could I use a subquery to get the desired results? Not sure how this would work.

---

quote:

---

Originally posted by dmoonme

SURVEYID,FIELDID,FIELDNAME,VALUE1,VALUE2,VALUE3
20,5,Accounting,Mentor,None,NULL
20,246,Spanish,Fluent,Fluent,Fluent
21,5,Accounting,None,None,NULL
21,246,Spanish,Limited,Limited,Limited

I included the fieldname in the sample data for explaination purposes.

What I want is someone who can mentor in accounting AND speaks spanish fluently.

BTW thanks for your replies.

---

SELECT * FROM YourTable t
INNER JOIN
(SELECT SURVEYID 
FROM YourTable
GROUP BY SURVEYID
HAVING SUM(CASE WHEN FIELDNAME='Accounting'
AND (VALUE1 LIKE '%mentor%' OR VALUE2 LIKE '%mentor%')
 THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN FIELDNAME='Spanish' 
AND (VALUE1 LIKE '%Fluent%' OR VALUE2 LIKE '%Fluent%')) 
THEN 1 ELSE 0 END)>0
)tmp
ON tmp.SURVEYID=t.SURVEYID

quote:

---

Originally posted by dmoonme

thanks visakh16 that works great if value1 and value2 are specified.

What if only one field and value1 and value2 are given?

In other words, someone who can mentor in accounting and any fluency level in spanish.

---