Re: Query having issues...

Chris,

I believe its the order of your "FROM" clause.

try

FROM contact co
LEFT JOIN account_info a ON (co.contact_id = a.contact_id)
LEFT JOIN company c ON (co.company_id = c.company_id)

Jim

> Hi All,
>
> I've got a PostgreSQL 7.2.1 server running on FreeBSD 4.7 PRERELEASE,
> with loads of memory and disk space, but I keep getting an error with this
> query and I can not, for the life of me, figure out what is causing it:
>
> 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;
>
> I keep getting this error:
>
> Relation "co" does not exist.
>
> But if I strip the query down to this:
>
> SELECT co.first_name, co.last_name, co.email_address
> FROM contact co, domain d
> WHERE d.domain_id = '666'
> AND d.company_id = co.company_id;
>
> It works with out a hitch, so I think I'm right in saying that the left
> joins are throwing it off somehow. The funny part is that I've been
> working with queries exactly like this first one with other areas of my
> database and they do not complain...
>
> any one got any ideas? Ran into this before? As far as I can tell that
> first query is right..
>
> Chris Bowlby,
> -----------------------------------------------------
> Manager of Information and Technology.
> excalibur(at)hub(dot)org
> www.hub.org
> 1-902-542-3657
> -----------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html