Обсуждение: Query having issues...
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
-----------------------------------------------------
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 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."
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