[Zope-DB] Trouble writing a left join using z sql method
Laura.McCord at doucet-austin.com
Thu Apr 29 12:25:09 EDT 2004
Ok, I was hoping there would be a shortcut so I didn't have to write out every table's fields.
Thanks for your help,
From: Charlie Clark [mailto:charlie at egenix.com]
Sent: Thu 4/29/2004 11:21 AM
To: Laura McCord
Cc: Charlie Clark
Subject: RE: [Zope-DB] Trouble writing a left join using z sql method
please always post to the list, no matter how trivial the question is. This
makes it easier for others in the future with a similar issue and believe me,
we all go through this.
On 2004-04-29 at 18:04:32 [+0200], Laura McCord wrote:
> Ok, but let me ask you if I have several fields that I have to display than
> do I have to list them all? If you look at my example the two tables are
> joined by the pcname. But, if I try to use the "select * from users left
> join .....where ...."
> I get an error I think because the pcname columns are in both tables and it
> gets confused. Have you ever dealt with this before? Because the select
> statement below works but I was trying to figure out if there is a more
> efficient way of doing this without listing all the fields.
> from users left join pcTable on users.pcname = pcTable.pcname
> where users.userid = '4'
This actually looks okay to me and I would an error that pcname is ambiguous
otherwise you shouldn't have any problems. You only Using explicit names for
tables is actually good practice but annoying. The only way around this is to
use SQL's AS function
from users AS u
left join pcTable AS pc on
users.pcname = pcTable.pcname
where users.userid = '4'
Hope that helps. By the way, are sure the last line should be string? '4' is
not the same as 4
Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
More information about the Zope-DB