Re: [GENERAL] Query having issues...

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