SubQuery Problem

[Kaur]

Hi,

I am having problem getting desired results from the second part of the

query with a subquery. My query looks like this.
SELECT tblSurveyResponse.SurveyID, tblSurveyResponse.SurveyEditionID,
tblSurveyResponse.QuestionID
FROM LkupQuestions INNER JOIN tblSurveyResponse ON
LkupQuestions.QuestionID = tblSurveyResponse.QuestionID
GROUP BY tblSurveyResponse.SurveyID, tblSurveyResponse.SurveyEditionID,

tblSurveyResponse.QuestionID
HAVING (((tblSurveyResponse.SurveyID) In (Select S.SurveyID from
tblSurvey S inner join tblSurveyEditions SE on S.SurveyID =
se.SurveyID group by S.SurveyID having Count(*) > 1 )));
This part works fine. This gives me a result set that looks like this.
Survey SurveyEdition Question SurveyResponse
1 1 1 1
1 2 1 2
2 1 1 7
2 2 2 3

From this result set I want to pick up the questions that exits in more

than one survey edition for a given survey. In this case it would Be
Question 1 and Survey 1 since Question 1 exists in both the editions of

Survey 1.
I donot seem to create a query that will retrieve this result set.

Any help would be appreciated.

 

[Michel Walsh]

Hi,


To get the questions that appear in more than one survey, you can test if
there is a record that has a different survey (that some survey actually
considered) with the same question (than the actual one):


SELECT DISTINCT question
FROM somewhere AS a
WHERE EXISTS( SELECT b.* FROM somewhere AS b
WHERE b.survey <> a.survey AND b.question =
a.question)



or you can do it with an outer join:

SELECT a.question
FROM somewhere AS a LEFT JOIN somewhere AS b
ON a.question = b.question AND a.survey<>b.survey
GROUP BY a.question
HAVING COUNT(b.question) <> 0


which is a little bit more shy to show its real intention, somehow. If there
is a match (by the ON clause), then b.question won't be null, and
COUNT(b.question) <> 0; on the other hand, if there is no match, b.* got
NULL in the result of the join, and counting null returns a count of 0.
Since a.survey<>b.survey destroys the possible reflexivity, no other care
has to be attended to the formulation (I think).


If you want the questions that appear only in one survey, just for the show
of a possible use of ALL, you can try:


SELECT question
FROM somewhere AS a
WHERE survey = ALL (SELECT b.survey FROM somewhere AS b
WHERE a.question=b.question)





Hoping it may help,
Vanderghast, Access MVP