Select based on multiple child records (SQL)

  • Thread starter Thread starter Chris Perry
  • Start date Start date
C

Chris Perry

Hello all,

I want to select certain child records from a table based on the values of
other unrelated child records.

For the select Im using three tables from a Questionnaire database.

Respondant, Response, and txtOption.

Each respondant, has multiple responses and each response can consist of one
or more txtOption.

I need to make a summary of certain responses for the Questionnaire, so for
example to find out what the answers where to the "size of business"
question I simply write a query like so :

SELECT txtOption.OptionValue
FROM (Respondant INNER JOIN Response ON Respondant.pkRespondantId =
Response.fkRespondantId) INNER JOIN txtOption ON
Response.pkResponseId = txtOption.fkResponseId
WHERE (Response.fkQuestionId=6);

But what I really need is to be able to select a subset of the responses
based on the OTHER responses to different questions, for example get the
size of business for those respondants thats are part of a certain industry
sector. Industry sector is one of the responses whose value is found in a
txtOption if you see what I mean.

Sorry if I'm confusing but as you can probably tell its a quite difficult
problem. Any help gratefully received.

Thank you.

Chris.
 
Hi Chris,

SELECT Response.pkResponseId, txtOption.OptionValue
FROM (Respondant INNER JOIN Response ON Respondant.pkRespondantId =
Response.fkRespondantId) INNER JOIN txtOption ON
Response.pkResponseId = txtOption.fkResponseId
WHERE (Response.fkQuestionId IN (6, 7, 8, 9));

Use the IN keyword at the end of statament.
Note, that I've added Response.pkResponseId field for clarity.
 
Hi Miha,

Thanks for your help. However your solution doesn't exactly solve my
problem. The thing is that I really want the values for all the responses to
one question, that is all the 'txtOption.OptionValue' rows related to all
the 'Response' rows with a certain 'fkQuestionId'.

But the real difficulty is that I want to select only those reponses where
certain values for other questions have been set. That is I only want those
responses for the respondants where the other responses have been a certain
way.

In other words for those people who answered x to question 1 and y to
question 2 what where their response to question 3.

Is this even possible with SQL ? If not I would be grateful if someone would
put me out of my misery ;-)

Where did I go wrong with my database design ?

Thanks again mate.
 
Hi Chris,

Chris Perry said:
Hi Miha,

Thanks for your help. However your solution doesn't exactly solve my
problem. The thing is that I really want the values for all the responses to
one question, that is all the 'txtOption.OptionValue' rows related to all
the 'Response' rows with a certain 'fkQuestionId'.

But the real difficulty is that I want to select only those reponses where
certain values for other questions have been set. That is I only want those
responses for the respondants where the other responses have been a certain
way.

In other words for those people who answered x to question 1 and y to
question 2 what where their response to question 3.
Click to expand...

This problem has two parts - one finding those people and two finding their
response to the q3.
1: Find the required responses.
select fkResponseId from
(select fkResponseId, Count(*) As Cnt from txtOption where txtOption.ID = 1
and txtOption.Answer = x or txtOption.ID = 2 and txtOption.Answer = y GROUP
BY fkResponseId HAVING Count(*) =2)

Once you have the responses, you can extract fkresponderid and check his/her
txtOption
select distinct fkPersonId from response where pkResponseId in (select
...... )

You can easily add inner joins to above select..
PS. The select might have syntax problems as I dropped it out of my head
directly :)
HTH
 
Hi Miha,

So a select subquery is the way to go ? Excellent I will give it a try. :-D

Thanks very much for your help.
 
Back
Top