Can A Single SQL Be Written

P

PC Datasheet

Given:
TblSurveyResponse
SurveyResponseID
ResponderID
SurveyItemID
ResponseYesNo

Can a single SQL be written to return all ResponderIDs who annswered Yes to
SurveyItemID 4 AND No to SurveyItemID 7?

Thanks!
 
J

JohnFol

yes, something like .

Select * from tblSurveyResponse where SurveyItemID = 4 and Response = Yes
and SurveyResponseID in (Select SurveyResponseID from tblSurveyResponse
where SurveyItemID = 7 and Response = No)
 
P

PC Datasheet

John,

Thanks for the fast response!

What would it look like to include SurveyItemID 9 and Response = Yes? (3
conditions)

And a fourth, SurveyItemID 11 and response = No?

Appreciate the help!!
 
G

giorgio rancati

Hi, PC Datasheet

2 conditions
----
Select ResponderId
from tblSurveyResponse
where SurveyItemID = 4 and ResponseYesNo = Yes
OR SurveyItemID = 7 and ResponseYesNo = No
group By ResponderId
having Count(*) = 2
----

3 conditions
----
Select ResponderId
from tblSurveyResponse
where SurveyItemID = 4 and ResponseYesNo = Yes
OR SurveyItemID = 7 and ResponseYesNo = No
OR SurveyItemID = 9 and ResponseYesNo = Yes
group By ResponderId
having Count(*) = 3
----

n conditions
----
Select ResponderId
from tblSurveyResponse
where SurveyItemID = 4 and ResponseYesNo = Yes
OR SurveyItemID = 7 and ResponseYesNo = No
OR SurveyItemID = 9 and ResponseYesNo = Yes
OR ...................
OR ...................
OR ...................
group By ResponderId
having Count(*) = n
 
W

Wolf

Select ResponderID,SurveyItemID,ResponseYesNo
From tblSurveyResponse as sr
Click to expand...
where (sr.SurveyItemID = 4 and sr.ResponseYesNo = 'Yes')
or (sr.SurveyItemID = 7 and sr.ResponseYesNo = 'No')
or (sr.SurveyItemID = 9 and sr.ResponseYesNo = 'Yes')
or (sr.SurveyItemID = 11 and sr.ResponseYesNo = 'No')
 
T

Tim Marshall

JohnFol said:
yes, something like .

Select * from tblSurveyResponse where SurveyItemID = 4 and Response = Yes
and SurveyResponseID in (Select SurveyResponseID from tblSurveyResponse
where SurveyItemID = 7 and Response = No)
Click to expand...

I didn't see anything in Steve's criteria about the need for
SurveyResponseID, so what about just using an or conjunction with
parenthesis? That would be my first crack:

Select ResponderID, SurveyItemID, Response from tblSurveyResponse where
(SurveyItemID = 4 and Response = Yes) or (SurveyItemID = 7 and Response
= Yes)

In Oracle, database tuning tehniques suggest using UNions rather than or
conjunctions in the where criteria and I know with huge databases in
Oracle the union method does run appreciably faster. Not sure about the
same in Jet, though, but here's what I'd try with a union:

Select
ResponderID, SurveyItemID, Response from tblSurveyResponse
where
SurveyItemID = 4 and Response = Yes
UNion
Select
ResponderID, SurveyItemID, Response from tblSurveyResponse
where
SurveyItemID = 7 and Response = Yes

The first method is easy in that you can use the query builder...
 
T

Tim Marshall

Sorry, the dangers of copy and paste - he following are corrected

Tim said:
Select ResponderID, SurveyItemID, Response from tblSurveyResponse where
(SurveyItemID = 4 and Response = Yes) or (SurveyItemID = 7 and Response
= Yes)
Click to expand...

Should be:

Select ResponderID, SurveyItemID, Response from tblSurveyResponse where
(SurveyItemID = 4 and Response = Yes) or (SurveyItemID = 7 and Response
= No)
Select
ResponderID, SurveyItemID, Response from tblSurveyResponse
where
SurveyItemID = 4 and Response = Yes
UNion
Select
ResponderID, SurveyItemID, Response from tblSurveyResponse
where
SurveyItemID = 7 and Response = Yes
Click to expand...

Should be:

Select
ResponderID, SurveyItemID, Response from tblSurveyResponse
where
SurveyItemID = 4 and Response = Yes
UNion
Select
ResponderID, SurveyItemID, Response from tblSurveyResponse
where
SurveyItemID = 7 and Response = No
 
T

Tim Marshall

ONce again, sorry. This is what happens when I read at 530 in the morning.

Forget all my crap and go with what Baz wrote.
 
B

Baz

PC Datasheet said:
Given:
TblSurveyResponse
SurveyResponseID
ResponderID
SurveyItemID
ResponseYesNo

Can a single SQL be written to return all ResponderIDs who annswered Yes to
SurveyItemID 4 AND No to SurveyItemID 7?

Thanks!
Click to expand...

Sub-query solutions will work logically, but they are desperately slow in
Access (although fine in SQL Server). This'll do the trick much more
efficiently:

SELECT SR1.ResponderID FROM tblSurveyResponse SR1 INNER JOIN
tblSurveyResponse SR2 ON SR1.ResponderID = SR2.ResponderID WHERE
SR1.SurveyItemID = 4 AND SR1.ResponseYesNo = Yes AND SR2.SurveyItemID = 7
AND SR2.ResponseYesNo = No

To add more tests, simply add more self-joins to the FROM clause and more
criteria to the WHERE clause.
 
P

PC Datasheet

Baz,

Thank you for your response! Is there a way to create the query in design
view or is it necessary to type in the SQL? Is there a way to get the query
names SR1 and SR2 in design view?

Thanks,

Steve
PC Datasheet
 
D

Douglas J Steele

It can be done in design view: simply drag tblSurveyResponse in twice.

To set SR1 and SR2, select the tables you just dragged in (one by one) and
look at the Properties: you need to set the Alias property.
 
P

PC Datasheet

Thanks, Doug!

Douglas J Steele said:
It can be done in design view: simply drag tblSurveyResponse in twice.

To set SR1 and SR2, select the tables you just dragged in (one by one) and
look at the Properties: you need to set the Alias property.
Click to expand...
 
B

Baz

PC Datasheet said:
Baz,

Thank you for your response! Is there a way to create the query in design
view or is it necessary to type in the SQL? Is there a way to get the query
names SR1 and SR2 in design view?

Thanks,

Steve
PC Datasheet
Click to expand...

What Doug said.

I've just gotta ask: is this some kind of spoof? I find it hard to imagine
the Steve/PC Datasheet that we know and love actually asking for help with
something. Just curious, you understand: you are most welcome to the help,
whoever you might be.
 
P

PC Datasheet

Curiosity killed the cat!!

Steve


Baz said:
What Doug said.

I've just gotta ask: is this some kind of spoof? I find it hard to
imagine
the Steve/PC Datasheet that we know and love actually asking for help with
something. Just curious, you understand: you are most welcome to the
help,
whoever you might be.
Click to expand...
 
Top