probably your DocReview ehere not Submitted by and AssignedTo or
Completed by the same person. I think you need a left join and table
renaming:
submit_team=db.Team.with_alias('submit_team')
assign_team=db.Team.with_alias('assign_team')
complete_team=db.Team.with_alias('complete_team')
rows=db().select
(db.ReviewType.ReviewTypeName,submit_team.LName,assign_team.LName,complete_team.LName,
left=(db.ReviewType.on(db.DocReview.ReviewType_id==db.ReviewType.id),
submit_team.on
(db.DocReview.ReviewSubmittedBy==submit_team.id),
submit_team.on(db.DocReview.ReviewAssignedBy==assign_team.id),
submit_team.on
(db.DocReview.ReviewCompletedBy==complete_team.id)))
On Mar 22, 4:05 pm, ctalley <[email protected]> wrote:
> No doubt this is off topic ... I'm pretty sure it's an issue with
> sqlite not web2py. But maybe someone's run into it before so I guess
> it doesn't hurt to ask.
>
> I'm using sqlite bundled with web2py. I have a "Team" table (each
> record has name, phone, e-mail, ...), and a "DocReview" table with
> three FK columns to people in Team. The following query, which joins
> all three FK references in DocReview with Team, returns ONE record
> with ReviewType and ONE name. I would expect ALL records - each with
> ReviewType and THREE names.
>
> rows=db((db.DocReview.ReviewType_id==db.ReviewType.id) &
> (db.DocReview.ReviewSubmittedBy==db.Team.id) &
> (db.DocReview.ReviewAssignedTo==db.Team.id) &
> (db.DocReview.ReviewCompletedBy==db.Team.id))
> .select(db.ReviewType.ReviewTypeName,
> db.Team.LName)
>
> This query, which joins only one of the FK references in DocReview
> with Team, returns ALL records with ReviewType and one name (as
> expected).
>
> rows=db((db.DocReview.ReviewType_id==db.ReviewType.id) &
> (db.DocReview.ReviewSubmittedBy==db.Team.id))
> .select(db.ReviewType.ReviewTypeName,
> db.Team.LName)
>
> Any way to get the first query to work as intended? Would it work
> with another database - maybe PostgreSQL?
>
> Any assistance is appreciated.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
