PostgreSQL select

On Sat, 16 Jul 2005, [ISO-8859-2] Ji�� N�mec wrote:

There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all
columns exist, I have no idea where's the problem :/ PostgreSQL
reports this error: ERROR: relation "fieldx" does not exist...

SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options
FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table
LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id

Our reading of the spec is that fieldx is not in scope for that left
join's on condition since effectively join has higher precedence than
comma separating items in the from list.

Am Samstag, den 16.07.2005, 09:19 +0200 schrieb Ji�� N�mec:

Hello,

There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all
columns exist, I have no idea where's the problem :/ PostgreSQL
reports this error: ERROR: relation "fieldx" does not exist...

SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options
FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table
LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id
WHERE fieldx.field_id = join_table.field_id AND join_table.group_id = 10
GROUP BY fieldx.field_id
ORDER BY fieldx.field_id

Any clues?

May we see \dt please?
I'm pretty sure fieldx does not exist. Maybe you have "FieldX" or
something instead?

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

On Sat, 16 Jul 2005, [ISO-8859-2] Ji�� N�mec wrote:

There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all
columns exist, I have no idea where's the problem :/ PostgreSQL
reports this error: ERROR: relation "fieldx" does not exist...

SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options
FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table
LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id

Our reading of the spec is that fieldx is not in scope for that left
join's on condition since effectively join has higher precedence than
comma separating items in the from list.

In other words: what you probably meant here is

FROM (c_custom_fields AS fieldx CROSS JOIN j_product_groups_fields AS join_table)
LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id

(The parentheses aren't required in this particular case but they help
clarify your intent.)

We are aware that there are certain standards-challenged products
that get this wrong.

regards, tom lane

16. července 2005, 16:46:59, napsal jste:

In other words: what you probably meant here is

FROM (c_custom_fields AS fieldx CROSS JOIN
j_product_groups_fields AS join_table)
LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id

Thank you, your query runs but returns weird records, returns correct
records from "c_custom_fields" table but incorrect number of records
from JOINed "c_custom_fields_options" table. There are 3 table such
structures:

c_custom_fields - field_id, field_name
1 RAM
2 HDD

c_custom_fields_options - option_id, field_id, option_value
1 1 128
2 1 512
3 1 1024
4 2 80
5 2 120
6 2 160
7 2 200

j_product_groups_fields - group_id, field_id
1 1
1 2

I need to select records from c_custom_fields table which belong to
group_id = 1 (j_product_groups_fields) and count number of options
which belong to selected field:

field_id field_name COUNT(c_custom_fields_options.option_id)

1 RAM 3
2 HDD 4

--
Jiří Němec, ICQ: 114651500
www.menea.cz - www stránky a aplikace