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

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