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

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