Thread: Re: [GENERAL] Query having issues...

On Fri, 13 Sep 2002, Jim Buttafuoco wrote:

Hi All,

 Ok, the order did have effect on the query, might I suggest that it
shouldn't matter :> (I'm CC'ing the hackers list as part of this response
in the hopes that someone over there will see my request :>)..


> 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@hub.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
>
>
>
>
>

 Chris Bowlby,
 -----------------------------------------------------
 Manager of Information and Technology.
 excalibur@hub.org
 www.hub.org
 1-902-542-3657
 -----------------------------------------------------

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