Chris Bowlby <excalibur@hub.org> writes:
> Ok, the order did have effect on the query, might I suggest that it
> shouldn't matter :>
If you think that, then you are wrong.
> SELECT co.first_name, co.last_name, co.email_address,
> a.password, c.company_number
> FROM contact co, domain d
> LEFT JOIN account_info a ON (co.contact_id = a.contact_id)
> LEFT JOIN company c ON (co.company_id = c.company_id)
> WHERE d.domain_id = '666'
> AND d.company_id = co.company_id;
The interpretation of this command per spec is
FROM
contact co,
((domain d LEFT JOIN account_info a ON (co.contact_id = a.contact_id))
LEFT JOIN company c ON (co.company_id = c.company_id))
which perhaps will make it a little clearer why co can't be referenced
where you are trying to reference it. A comma is not the same as a JOIN
operator; it has much lower precedence.
It would be legal to do this:
FROM contact co JOIN domain d ON (d.company_id = co.company_id)
LEFT JOIN account_info a ON (co.contact_id = a.contact_id)
LEFT JOIN company c ON (co.company_id = c.company_id)
WHERE d.domain_id = '666';
This gets implicitly parenthesized left-to-right as
FROM ((contact co JOIN domain d ON (d.company_id = co.company_id))
LEFT JOIN account_info a ON (co.contact_id = a.contact_id))
LEFT JOIN company c ON (co.company_id = c.company_id)
regards, tom lane
