
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
 -----------------------------------------------------


Fri, 13 Sep 2002 10:25:15 -0300 (ADT)
Chris Bowlby <excalibur@hub.org>
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@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





Fri, 13 Sep 2002 09:47:28 -0400
"Jim Buttafuoco" <jim@spectrumtelecorp.com>
Re: Query having issues...
Chris Bowlby dijo: 

>      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;

Note that you are JOINing "domain d" with "account_info a"; by the time
this is looked up, there is no relation co there, so the qualification
doesn't make sense.  Try this:

      SELECT co.first_name, co.last_name, co.email_address,
             a.password, c.company_number
      FROM domain d, 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)
      WHERE d.domain_id = '666'
      AND d.company_id = co.company_id;

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell."


Fri, 13 Sep 2002 10:57:39 -0400 (CLT)
Alvaro Herrera <alvherre@atentus.com>
Re: 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
 -----------------------------------------------------


Sat, 14 Sep 2002 16:06:21 -0300 (ADT)
Chris Bowlby <excalibur@hub.org>
Re: 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

Sat, 14 Sep 2002 15:21:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Query having issues... 