is it what you want?:
SELECT quiz.id,
quiz.name,
count(question.quiz_id) AS num_q,
count(result.passed) AS num_passed
FROM quiz
JOIN question ON question.quiz_id=quiz.id
JOIN result ON result.quiz_id=quiz.id
GROUP BY quiz.id, quiz.name
-- or the same:
SELECT quiz.id,
quiz.name,
count(question.quiz_id) AS num_q,
count(result.passed) AS num_passed
FROM quiz
WHERE question.quiz_id=quiz.id
AND result.quiz_id=quiz.id
GROUP BY quiz.id, quiz.name
-- web2py
db((question.quiz_id == quiz.id ) & (result.quiz_id == quiz.id)).select(
groupby=[db.quiz.id, db.quiz.name])
On Monday, February 29, 2016 at 9:33:31 PM UTC+3, Bill Black wrote:
>
> // I've left out fields that are irrelevant to the problem
>
> Table quiz | name (varchar)
> Table question | quiz_id (fk)
> Table result | quiz_id (fk), passed (boolean)
>
> quiz - question, quiz - result are one to many relations.
>
> I'm trying to get the 'quiz name, # of questions in the quiz, # of passed
> (result) for the quiz'
>
>
> On Monday, February 29, 2016 at 12:43:41 PM UTC-5, Val K wrote:
>>
>> Hi!
>> There are many aliases with sub select in your query, it's difficult to
>> understand tables structure.
>> Post tables definition and some comment about what do you want to do,
>> please
>>
>>
>> On Monday, February 29, 2016 at 8:11:09 AM UTC+3, Bill Black wrote:
>>>
>>> SELECT a.*,b.num_passed
>>> FROM (SELECT q.id,q.name,count(t.id) AS num_q
>>> FROM quiz AS q
>>> JOIN question AS t ON q.id=t.quiz_id GROUP BY q.id)
>>> AS a
>>> JOIN (SELECT quiz_id,count(passed) AS num_passed FROM result GROUP BY
>>> quiz_id)
>>> AS b ON a.id=b.quiz_id;
>>> I need to join these two selects.
>>> I've spent all morning trying to get it to work in dal. But I don't
>>> think it can be done...
>>> Just posting here in case there is a way to do this before resorting to
>>> executesql.
>>> Thanks.
>>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
