[Zope-DB] Trouble writing a left join using z sql method

Laura McCord 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,
 Laura

________________________________

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



Hi Laura,

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.
> select
> users.userid,
> users.username,
> users.useremail,
> users.useroffice,
> users.userpwd,
> users.userpwdchg,
> users.pcname,
> pcTable.pcmake,
> pcTable.pcmodel,
> pcTable.pcserial,
> pcTable.pcmhz,
> pcTable.pcmbram,
> pcTable.pc_oem_os,
> pcTable.pc_oem_os_key,
> pcTable.pc_ip_add
> 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

SELECT
select
u.userid,
u.username,
u.useremail,
u.useroffice,
u.userpwd,
u.userpwdchg,
u.pcname,
pc.pcmake,
pc.pcmodel,
pc.pcserial,
pc.pcmhz,
pc.pcmbram,
pc.pc_oem_os,
pc.pc_oem_os_key,
pc.pc_ip_add
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

Charlie
--
Charlie Clark
eGenix.com

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 mailing list