Thread: left outer join fails because "column .. does not exist in left table?"
left outer join fails because "column .. does not exist in left table?"
From
Rick.Casey@colorado.edu
Date:
I have a JOIN error that is rather opaque...at least to me. I've using other JOIN queries on this project, which seem very similar to this one, which looks like: SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid) WHERE D.subjectidkey=S.id AND STY.studyindex=D.studyindex AND IPJ.projects_index=P.ibg_projects_index ORDER BY studyabrv,boxnumber,wellcolumn,wellrow But when I run it I get this error: ERROR: column "dnasampleid" specified in USING clause does not exist in left table I am rather mystified by this, since this field is definitely in the dnasample table, as the primary key. Nor do not see how to start debugging such an error. Any suggestions appreciated... --Rick
Re: left outer join fails because "column .. does not exist in left table?"
From
Scott Marlowe
Date:
On Wed, Jun 30, 2010 at 7:01 PM, <Rick.Casey@colorado.edu> wrote: > I have a JOIN error that is rather opaque...at least to me. > > I've using other JOIN queries on this project, which seem very similar to > this one, which looks like: > > SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn > FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P > LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid) > WHERE > D.subjectidkey=S.id > AND STY.studyindex=D.studyindex > AND IPJ.projects_index=P.ibg_projects_index > ORDER BY studyabrv,boxnumber,wellcolumn,wellrow > > But when I run it I get this error: > > ERROR: column "dnasampleid" specified in USING clause does not exist in > left table > > I am rather mystified by this, since this field is definitely in the > dnasample table, as the primary key. Nor do not see how to start debugging > such an error. Any suggestions appreciated... Capitalization maybe? pgsql folds to lower case.
Rick.Casey@colorado.edu writes: > SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn > FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P > LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid) > WHERE > D.subjectidkey=S.id > AND STY.studyindex=D.studyindex > AND IPJ.projects_index=P.ibg_projects_index > ORDER BY studyabrv,boxnumber,wellcolumn,wellrow > ERROR: column "dnasampleid" specified in USING clause does not exist in > left table > I am rather mystified by this, since this field is definitely in the > dnasample table, as the primary key. It appears you're used to mysql, which processes commas and JOINs left-to-right (more or less, I've never bothered to figure out their behavior exactly). We follow the SQL standard, which says JOIN binds tighter than comma. Therefore, the left-hand argument of the JOIN is only ibg_projects not the cross join of DNASample x IBG_Studies x Subjects x ibg_projects. You could probably get the behavior you're expecting by writing ... FROM (DNASample D CROSS JOIN IBG_Studies STY CROSS JOIN Subjects S CROSS JOIN ibg_projects P) LEFT OUTER JOIN ibg_ps_join IPJ USING ... Or it might be enough to rearrange to ... FROM DNASample D LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid), IBG_Studies STY, Subjects S, ibg_projects P WHERE ... regards, tom lane
Re: left outer join fails because "column .. does not exist in left table?"
From
Scott Marlowe
Date:
On Wed, Jun 30, 2010 at 8:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Rick.Casey@colorado.edu writes: >> SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn >> FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P >> LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid) >> WHERE >> D.subjectidkey=S.id >> AND STY.studyindex=D.studyindex >> AND IPJ.projects_index=P.ibg_projects_index >> ORDER BY studyabrv,boxnumber,wellcolumn,wellrow >> ERROR: column "dnasampleid" specified in USING clause does not exist in >> left table > >> I am rather mystified by this, since this field is definitely in the >> dnasample table, as the primary key. > > It appears you're used to mysql, which processes commas and JOINs > left-to-right (more or less, I've never bothered to figure out their > behavior exactly). Note that even MySQL now follows the standard on this, without needing some special strict switch or anything. Of course, a lot of folks are still using older versions that are in fact still broken.