Обсуждение: 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