Re: Complicated query
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Mon, 08 Dec 2014 22:07:20 -0500
Message-ID: <m65p0m$c58$1_at_dont-email.me>
>> On Sat, 06 Dec 2014 08:28:18 -0500, 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.
>>
>> I know this will piss off normalized Jerry Stuckle, but I'd just as soon
>> use one table.
>> Columns would be
>> ID
>> Question
>> Achoice
>> Bchoice
>> Cchoice
>> Dchoice
>> Correct
>>
>> Now all you need to do is retrieve the row for each ID as needed.
>> Display what you need to per ID.
>> MYSQL can handle that with no problem.
Date: Mon, 08 Dec 2014 22:07:20 -0500
Message-ID: <m65p0m$c58$1_at_dont-email.me>
On 12/8/2014 10:00 PM, Shelly wrote:
> On 12/7/2014 12:05 PM, richard wrote:
>> On Sat, 06 Dec 2014 08:28:18 -0500, 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.
>>
>> I know this will piss off normalized Jerry Stuckle, but I'd just as soon
>> use one table.
>> Columns would be
>> ID
>> Question
>> Achoice
>> Bchoice
>> Cchoice
>> Dchoice
>> Correct
>>
>> Now all you need to do is retrieve the row for each ID as needed.
>> Display what you need to per ID.
>> MYSQL can handle that with no problem.
> > Of course, and if *I* had designed the table structures, that is what I > would have done as well. Sometimes, however, you step into an > established situation and you just have to make the best of it. That > was the case here. > > Oh, and let PITAs roll off your back. It is healthier. >
It's a good thing someone who knew how to correctly design a database created it, then.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Tue Dec 09 2014 - 04:07:20 CET