Thread: PostgreSQL select
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? -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace
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