Re: Complicated query
From: Luuk <luuk_at_invalid.lan>
Date: Sat, 06 Dec 2014 16:46:40 +0100
Message-ID: <548324e0$0$2974$e4fe514c_at_news.xs4all.nl>
>> On 12/6/2014 8:28 AM, Shelly wrote:
>>> I have finally come across a desired query that I have no idea on how to
>>> do -- or if it is even possible. Here is the scenario:
>>>
>>> Table Instances
>>> ===============
>>> id
>>> column1
>>> column2
>>> (and more)
>>>
>>> Table Questions
>>> ===============
>>> Id
>>> Question Text
>>>
>>> Table Answers
>>> =============
>>> Id
>>> QuestionId (Id in Questions)
>>> InstanceId (Id in Instances)
>>>
>>> Table Questions has Q entries. (Lets say just three for now.) What I
>>> want is to pull where each record contains:
>>>
>>> Table Instances has N entries.
>>>
>>> Table Answers has Q x N entries.
>>>
>>> I want to pull N records where each record has:
>>> column1, column2, Answer1, Answer2, Answer3
>>>
>>> In other words, I want all the answers (Q of them) for a given instance
>>> to be in a single returned record, not one answer in each of Q records
>>> returned.
>>>
>>
>> After some research I found a good solution on the net. (Note: I
>> inadvertently left out that the Answers table has a column "Answer" when
>> I posed the original question).
>>
>> This is for Oracle. The solution is:
>>
>> with data
>> as
>> (
>> select I.*, A.answer, row_number() over (partition by
>> observation_card_id order by answer) rn,
>> count(*) over (partition by A.InstanceId) cnt
>> from Answers A
>> join Instances I on (I.ID = A.InstanceId)
>> order by A.QUESTION_ID
>> )
>> select D.*, ltrim(sys_connect_by_path(D.Answer,','),',') Answers
>> from data D
>> where rn = cnt
>> start with rn = 1
>> connect by prior D.Id = D.Id and prior rn = rn-1
>> order by D.Id
>>
>> The answers are all in one field, Answeers, and it is a comma separated
>> string.
>>
>> Thank you to Joe Stefanelli who answered someone else about this on
>> stackoverflow.com.
>>
Date: Sat, 06 Dec 2014 16:46:40 +0100
Message-ID: <548324e0$0$2974$e4fe514c_at_news.xs4all.nl>
On 6-12-2014 15:45, The Natural Philosopher wrote:
> On 06/12/14 14:16, Shelly wrote:
>> On 12/6/2014 8:28 AM, Shelly wrote:
>>> I have finally come across a desired query that I have no idea on how to
>>> do -- or if it is even possible. Here is the scenario:
>>>
>>> Table Instances
>>> ===============
>>> id
>>> column1
>>> column2
>>> (and more)
>>>
>>> Table Questions
>>> ===============
>>> Id
>>> Question Text
>>>
>>> Table Answers
>>> =============
>>> Id
>>> QuestionId (Id in Questions)
>>> InstanceId (Id in Instances)
>>>
>>> Table Questions has Q entries. (Lets say just three for now.) What I
>>> want is to pull where each record contains:
>>>
>>> Table Instances has N entries.
>>>
>>> Table Answers has Q x N entries.
>>>
>>> I want to pull N records where each record has:
>>> column1, column2, Answer1, Answer2, Answer3
>>>
>>> In other words, I want all the answers (Q of them) for a given instance
>>> to be in a single returned record, not one answer in each of Q records
>>> returned.
>>>
>>
>> After some research I found a good solution on the net. (Note: I
>> inadvertently left out that the Answers table has a column "Answer" when
>> I posed the original question).
>>
>> This is for Oracle. The solution is:
>>
>> with data
>> as
>> (
>> select I.*, A.answer, row_number() over (partition by
>> observation_card_id order by answer) rn,
>> count(*) over (partition by A.InstanceId) cnt
>> from Answers A
>> join Instances I on (I.ID = A.InstanceId)
>> order by A.QUESTION_ID
>> )
>> select D.*, ltrim(sys_connect_by_path(D.Answer,','),',') Answers
>> from data D
>> where rn = cnt
>> start with rn = 1
>> connect by prior D.Id = D.Id and prior rn = rn-1
>> order by D.Id
>>
>> The answers are all in one field, Answeers, and it is a comma separated
>> string.
>>
>> Thank you to Joe Stefanelli who answered someone else about this on
>> stackoverflow.com.
>>
> This is the sort of query where I have found that doing it all in > application software using multiple queries is quicker, for sub zillion > records, than expecting mysql to do it in hours.. > > For < zillion records, buy oracle and a very powerful computer... > > Don't get me wrong. I love mysql. But it is not best when optimising and > executing complex queries. And its not nearly as good at it as a custom > program written in (e.g.) C, is. > > >
The query is not complex.
The database is....
Can you define 'Instance'? is that the item that someone starts to answer questions?
Can you define 'Answers'? Are those the possible answers (multiple choice) for a question? or the given answers in this instance.
This 'complex' problem has nothing to do with mysql being 'less' able to do this kind of questions. Received on Sat Dec 06 2014 - 16:46:40 CET