// 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.
