Rick.Casey@xxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
