Re: Complicated query

From: Shelly <sheldonlg_at_thevillages.net>
Date: Sat, 06 Dec 2014 12:20:06 -0500
Message-ID: <m5vdre$p8j$1_at_dont-email.me>


On 12/6/2014 10:55 AM, Jerry Stuckle wrote:

> On 12/6/2014 9:16 AM, 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.
>>
>
> Shelly,
>
> Unfortunately, this uses recursive SQL, which MySQL doesn't support.
> However, you should be able to do it with the GROUP_CONCAT function in
> MySQL - see the manual.
>
> I don't have time right now to work the entire query out (wife is
> pushing me to go shopping :) ), but I can take a look at it later today
> when we get back.
>
> BTW - I have never found it faster to run multiple queries from a C
> program than let MySQL do it itself.  This is especially true if you're
> using a remote server.  The fact a complex query takes so long is a sure
> indication of a design issue.

No need to bother, Jerry, as I posted the solution already. I agree with you in that I almost always find that a query does it faster than multiple queries with intermediate code.

BTW, I first found the solution online for GROUP_CONCAT, but Oracle does not have that function and I am using Oracle. I found the solution I posted online as well.

BTW, how is the php group doing these days? I still have a contract (almost 7 years now) with the Fortune 500 company that I got through posting to that group.

-- 
Shelly
Received on Sat Dec 06 2014 - 18:20:06 CET

Original text of this message