Can A Single SQL Be Written

  • Thread starter Thread starter PC Datasheet
  • Start date Start date
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!
 
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)
 
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!!
 
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')
 
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...
 
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
 
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.
 
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.
 
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
 
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.
 
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...
 
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.
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sql and label counting 1
Use value of a variable, as the field name. 9
Excel Wildcard in Excel array formula 0
using sql server function 2
ODBC: Delete Query how to? 8
troubles with dns-less connection to sql 0
Does SQL Run Behind Queries? 3
read sql record into memory, use/modify fields then save changes 3

Back
Top