Multiple where clause criteria

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)

Multiple where clause criteria

Author

Topic

rlhowell
Starting Member

1 Post

Posted - 2005-04-20 : 11:43:57

Hi all,

I'm having a problem working the SQL out for the following:

I have a table Person:

personId----------name
1----------------Robert
2----------------Frank

and another table Question

personId------questionId---------answer
1-------------1-----------------Y
1-------------2-----------------Y
1-------------3-----------------Y
2-------------1-----------------Y
2-------------2-----------------Y
2-------------3-----------------N

and I am trying to find a way of returning a list of ids of people who have answered Y to the first and second questions but N to the third.

If I try:

SELECT DISTINCT personId FROM Person INNER JOIN
Question ON question.personId = Person.personId
WHERE (Question.questionId = '1') AND (Question.answer = 'Y') AND (Question.questionId = '2') AND (Question.answer = 'Y') AND (Question.questionId = '3') AND (Question.answer = 'N')

I get no records returned.

Anyone's help here would be much appreciated as this is driving me mad.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-20 : 23:26:11

at first glance I think you need to join the table three times

select personId from person p, question q1, question q2, question q3
where (person.id = q1.personid and q1.questionid = 1 and q1.answer = 'Y')
and (person.id = q2.personid and q2.questionid = 2 and q2.answer = 'Y')
and (person.id = q3.personid and q3.questionid = 3 and q3.answer = 'N')

try that and let me know how you go (sorry can't test it)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-21 : 02:19:40

This should do it:

SELECT
	a.personId
FROM
	Person a
	INNER JOIN
	Question b
	ON b.personId = a.personId
where
	b.questionId in (1,2,3)
group by
	a.personId
having
	sum(
	case
	when b.questionId in (1,2) and b.answer = 'Y'
	then 1
	when b.questionId in (3) and b.answer = 'N'
	then 1
	else 0
	end) = 3

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources