This may make me sound like a horrible developer, but I never use INNER joins. And when I say never, I mean NEVER. I have nothing against them, it's just that I can accomplish everything I want to do with LEFT joins. In your example for joining table class_groups I'd do something like this instead:
LEFT OUTER JOIN class_groups cg ON c.id = cg.class_id And then in my WHERE clause I'd include: AND cg.class_id > 0 Then, once you have it rewritten using LEFT joins only, it should be trivial to convert that to the DAL select statement. Probably not ideal, but this is what I'd do. -Jim On Friday, September 11, 2020 at 8:54:41 AM UTC-5, lucas wrote: > > hey all, > > i know i can run the following raw postgresql statement with > db.executesql. but is there a way to do this multiple inner and one left > outer join using standard db((...) & (...) & ...).select(... > left=db.folm((...) &(...) & ...)) kind of syntax somehow? i tried the > various obvious ways but it either crashes the server with 502 or gives the > wrong results. thanx in advance, lucas > > select c.title, c.subtitle, cg.id, cg.name, u.last_name, u.first_name, > l.user_id, l.date_generated, l.date_completed, l.lab_manual, l.lab > from classes c > inner join class_groups cg on (c.id = cg.class_id) > inner join class_group_users cgu on (cg.id = cgu.class_group_id) and > (cgu.accepted = 'T') > inner join auth_user u on (cgu.user_id = u.id) > left outer join folm l on (cgu.id = l.class_group_user_id) and > (l.lab_manual = 'manual1') and (l.lab = 'FOLM01.html') > where (c.lab_manual like '%CHM1025%') > order by cg.id, u.last_name, u.first_name; > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/cde50904-c7f0-49ac-a5bf-568a9b33d516o%40googlegroups.com.
