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
