I get the following: SELECT Calls.id, Calls.CallerName, Calls.CreatedBy, Calls.HandledBy, Users.Name, Users.Name FROM Users, Calls LEFT JOIN Users AS created_by ON created_by.id=Calls.CreatedBy LEFT JOIN Users AS handled_by ON handled_by.id=Calls.HandledBy WHERE Calls.id>0;
On Mar 23, 6:06 pm, mdipierro <[email protected]> wrote: > hmmm. it could be a bug. What do you get if you do: > > print > db(db.Calls.id>0)._select(db.Calls.ALL,db.created_by.name,db.handled_by.name, > left=[created_by.on(created_by.id==db.Calls.CreatedBy), > handled_by.on(handled_by.id==db.Calls.HandledBy)]) > > On Mar 23, 11:31 am, jonatron <[email protected]> wrote: > > > Thanks, > > > This is close, but it does not quite get the result I'm looking for, I > > basically need the following MySQL query: > > > SELECT Calls . * , created_by.Name, handled_by.Name > > FROM ( > > Calls > > LEFT JOIN Users AS created_by ON Calls.CreatedBy = created_by.ID > > ) > > LEFT JOIN Users AS handled_by ON Calls.HandledBy = handled_by.ID; > > > I've tried adapting the code you posted but I can't seem to get the > > result set I need. For example a call from 'Helen' created by Bob and > > Handled by Sam, I would like to get the results: > > > CallerName created_by.Name handled_by.Name > > Helen Bob Sam > > > but I get > > > Calls.id Calls.CallerName Users.Name Users.Name > > 1 Helen Bob Bob > > 1 Helen Sam Sam > > 1 Helen Charlie Charlie > > > Any ideas? > > > On Mar 20, 3:10 pm, mdipierro <[email protected]> wrote: > > > > created_by=db.Users.with_alias('created_by') > > > handled_by=db.Users.with_alias('handled_by') > > > rows = > > > db(db.Calls.id>0).select(db.Calls.ALL,db.created_by.name,db.handled_by.name, > > > left=[created_by.on(created_by.id==db.Calls.CreatedBy), > > > handled_by.on(handled_by.id==db.Calls.HandledBy)]) > > > > On Mar 20, 6:58 am, jonatron <[email protected]> wrote: > > > > > Hi, > > > > > I am hoping someone can help with this: > > > > > I have tables setup something like this (this is a simplified > > > > representation): > > > > > db.define_table('Users', > > > > Field('Name')) > > > > > db.define_table('Calls', > > > > Field('CallerName'), > > > > Field('CreatedBy', db.Users), > > > > Field('HandledBy', db.Users)) > > > > > I need to produce a query set to send to geraldo reports that joins > > > > both the createdby and handledby fields to db.users.id so that I can > > > > display the users name in the report not the id. I can do one inner > > > > join no problem and use Users.Name to get the name. To do both joins > > > > and get useful data I think I need to do something analogue SQL AS on > > > > the joins and then access the data using an alias. I have played with > > > > with_alias but don't seem to get what I need. Can anyone point me in > > > > the right direction? -- You received this message because you are subscribed to the Google Groups "web2py-users" 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.
