Re: left outer join fails because "column .. does not exist in left table?"

Rick(dot)Casey(at)colorado(dot)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