Thread: missing FROM-clause notice but nothing is missing ...
I get a "missing FROM-clause" with the following query. I don't see why
as prod_id is a FK in the supposedly missing table ...
I also get an error on my ORDER by, even though I am ordering on
products.id which is what both selects retrieve ...
This looks like a simple thing but I cannot figure out what I missed ....
DB=# SELECT products.id
FROM products
WHERE name ILIKE 'AA'
UNION
SELECT prod_id
FROM rel_cast_crew_movies
WHERE cast_crew_id=1012
ORDER BY products.id;
NOTICE: Adding missing FROM-clause entry for table "products"
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
I even tried this convoluted query to get around the notice and error
but to no avail:
DB=# SELECT products.id
FROM products
WHERE name ILIKE 'AA'
UNION
SELECT products.id
FROM rel_cast_crew_movies, products
WHERE cast_crew_id=1012
AND prod_id=products.id
ORDER BY products.id;
The tables:
DB=# \d rel_cast_crew_movies
Table "public.rel_cast_crew_movies"
Column | Type | Modifiers
--------------+---------+-----------
prod_id | integer | not null
cast_crew_id | integer | not null
Indexes: rel_cast_crew_movies_pkey primary key btree (cast_crew_id, prod_id)
Foreign Key constraints: $1 FOREIGN KEY (prod_id) REFERENCES
products(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
DEFERRED,
$2 FOREIGN KEY (cast_crew_id) REFERENCES
cast_crew(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
DEFERRED
I think you'll find life easier if create a view then operate on that
view.
create view v_products_cast_crews AS
SELECT p.id AS products,
p.name AS product_name,
cc.id AS cast_crew,
cc.name AS cast_name
FROM rel_cast_crew_movies AS rccm
LEFT JOIN products AS p ON p.id = rccm.prod_id
LEFT JOIN cast_crew AS cc ON cc.id = rccm.cast_crew_id;
Then you can do this;
select * from v_products_cast_crews where product_name ILIKE 'AA' AND
cast_crew = 1012 ORDER BY products;
Cheers.
On Fri, 2003-03-28 at 14:29, Jean-Christian Imbeault wrote:
> I get a "missing FROM-clause" with the following query. I don't see why
> as prod_id is a FK in the supposedly missing table ...
>
> I also get an error on my ORDER by, even though I am ordering on
> products.id which is what both selects retrieve ...
>
> This looks like a simple thing but I cannot figure out what I missed ....
>
> DB=# SELECT products.id
> FROM products
> WHERE name ILIKE 'AA'
>
> UNION
>
> SELECT prod_id
> FROM rel_cast_crew_movies
> WHERE cast_crew_id=1012
> ORDER BY products.id;
>
> NOTICE: Adding missing FROM-clause entry for table "products"
> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> the result columns
>
>
> I even tried this convoluted query to get around the notice and error
> but to no avail:
>
>
> DB=# SELECT products.id
> FROM products
> WHERE name ILIKE 'AA'
>
> UNION
>
> SELECT products.id
> FROM rel_cast_crew_movies, products
> WHERE cast_crew_id=1012
> AND prod_id=products.id
> ORDER BY products.id;
>
>
> The tables:
>
> DB=# \d rel_cast_crew_movies
> Table "public.rel_cast_crew_movies"
> Column | Type | Modifiers
> --------------+---------+-----------
> prod_id | integer | not null
> cast_crew_id | integer | not null
> Indexes: rel_cast_crew_movies_pkey primary key btree (cast_crew_id, prod_id)
> Foreign Key constraints: $1 FOREIGN KEY (prod_id) REFERENCES
> products(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
> DEFERRED,
> $2 FOREIGN KEY (cast_crew_id) REFERENCES
> cast_crew(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
> DEFERRED
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.
Hadley Willan wrote: > > I think you'll find life easier if create a view then operate on that > view. Ok, I'll try that. I've been avoiding views because I don't understand them well or when/why to use them ... But I still am confused as to why postgres give me a notice and an error. I don't see anything wrong with my query. Jean-Christian Imbeault
No go with the view ... same error:
DB=# SELECT products.id
FROM products
WHERE name ILIKE 'AA'
UNION
SELECT prod_id
FROM v_products_cast_crews
WHERE cast_crew=1012
ORDER BY products.id;
NOTICE: Adding missing FROM-clause entry for table "products"
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote: > I get a "missing FROM-clause" with the following query. I don't see why > as prod_id is a FK in the supposedly missing table ... > > I also get an error on my ORDER by, even though I am ordering on > products.id which is what both selects retrieve ... > > This looks like a simple thing but I cannot figure out what I missed .... > > DB=# SELECT products.id > FROM products > WHERE name ILIKE 'AA' > > UNION > > SELECT prod_id > FROM rel_cast_crew_movies > WHERE cast_crew_id=1012 > ORDER BY products.id; > > NOTICE: Adding missing FROM-clause entry for table "products" > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of > the result columns I think this is because by the time you get to the order by, products and rel_cast_crew_movies are treated as being out of scope. The column produced by the union is just named "id" I think.
Stephan Szabo wrote: > > I think this is because by the time you get to the order by, products and > rel_cast_crew_movies are treated as being out of scope. The column > produced by the union is just named "id" I think. You're right. changing the ORDER by products.id to simply ORDER by id solved the problem ... I don't know much about SQL scoping but I would have hoped that a UNION could have kept the fully-qualified column names (i.e. products.id instead of simply ID). Jc
On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote: > Stephan Szabo wrote: > > > > I think this is because by the time you get to the order by, products and > > rel_cast_crew_movies are treated as being out of scope. The column > > produced by the union is just named "id" I think. > > You're right. changing the ORDER by products.id to simply ORDER by id > solved the problem ... > > I don't know much about SQL scoping but I would have hoped that a UNION > could have kept the fully-qualified column names (i.e. products.id > instead of simply ID). I believe the appropriate portion of the rules is (7.10 SR12b) 1) Let C be the <column name> of the i-th column of T1. If the <column name> of the i-th column of T2 is C, then the <column name> of the i-th column of TR is C. 2) Otherwise, the <column name> of the i-th column of TR is implementation-dependent and different from the <column name> of any column, other than itself, of any table referenced by any <table reference> contained in the SQL-statement. Note that AFAIK the column name reference above is just the column name not a qualified name.
Jean-Christian Imbeault wrote: > No go with the view ... same error: > > DB=# SELECT products.id > FROM products > WHERE name ILIKE 'AA' > > UNION > > SELECT prod_id > FROM v_products_cast_crews > WHERE cast_crew=1012 > > ORDER BY products.id; > > NOTICE: Adding missing FROM-clause entry for table "products" > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of > the result columns > Strange, I actually quickly whipped up the tables and it worked for me? You did adjust some of the column names appropriately? Also, the LEFT JOIN's are best treated as a single line too. Anyway, I looked further in and I see you've got it working. Still, views are a more convenient way than doing manual UNIONs all the time. H
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes: > No go with the view ... same error: > > DB=# SELECT products.id > FROM products > WHERE name ILIKE 'AA' > > UNION > > SELECT prod_id > FROM v_products_cast_crews > WHERE cast_crew=1012 > > ORDER BY products.id; > > NOTICE: Adding missing FROM-clause entry for table "products" > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the > result columns You could do "ORDER BY 1" to sort based on the first column, or you could just remove the word "products." in both places. It should work if you say "ORDER BY id". I would put AS id in the second select for clarity for the next reader though. Incidentally, are you sure you want UNION and not UNION ALL. The former has to exclude overlaps and that's a lot of extra work. -- greg
Try: select id from ( SELECT products.id as id FROM products WHERE name ILIKE 'AA' UNION SELECT prod_id as id FROM rel_cast_crew_movies WHERE cast_crew_id=1012 ) as ss ORDER BY id; Jean-Christian Imbeault wrote: > > I get a "missing FROM-clause" with the following query. I don't see why > as prod_id is a FK in the supposedly missing table ... > > I also get an error on my ORDER by, even though I am ordering on > products.id which is what both selects retrieve ... > > This looks like a simple thing but I cannot figure out what I missed .... > > DB=# SELECT products.id > FROM products > WHERE name ILIKE 'AA' > > UNION > > SELECT prod_id > FROM rel_cast_crew_movies > WHERE cast_crew_id=1012 > ORDER BY products.id; > > NOTICE: Adding missing FROM-clause entry for table "products" > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of > the result columns > > I even tried this convoluted query to get around the notice and error > but to no avail: > > DB=# SELECT products.id > FROM products > WHERE name ILIKE 'AA' > > UNION > > SELECT products.id > FROM rel_cast_crew_movies, products > WHERE cast_crew_id=1012 > AND prod_id=products.id > ORDER BY products.id; > > The tables: > > DB=# \d rel_cast_crew_movies > Table "public.rel_cast_crew_movies" > Column | Type | Modifiers > --------------+---------+----------- > prod_id | integer | not null > cast_crew_id | integer | not null > Indexes: rel_cast_crew_movies_pkey primary key btree (cast_crew_id, prod_id) > Foreign Key constraints: $1 FOREIGN KEY (prod_id) REFERENCES > products(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY > DEFERRED, > $2 FOREIGN KEY (cast_crew_id) REFERENCES > cast_crew(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY > DEFERRED > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote:
> Stephan Szabo wrote:
> >
> > I think this is because by the time you get to the order by, products and
> > rel_cast_crew_movies are treated as being out of scope. The column
> > produced by the union is just named "id" I think.
>
> You're right. changing the ORDER by products.id to simply ORDER by id
> solved the problem ...
>
> I don't know much about SQL scoping but I would have hoped that a UNION
> could have kept the fully-qualified column names (i.e. products.id
> instead of simply ID).
Not, that would actually be semantically incorrect. The query you're
listing works kinda like this:
(
(SELECT products.id FROM products WHERE name ILIKE 'AA')
UNION
(SELECT prod_id FROM rel_cast_crew_movies WHERE cast_crew_id=1012)
)
ORDER BY <fieldnamegoeshere>;
The point I'm making is that when you union those two select statements,
the result fields CAN'T be called either products.id or
rel_cast_crew_movies.prod_id, because you've unioned those two datasets.
Both names would be incorrect. So, the parser picks the unqualified name
of the first field to call the resultant field. Note you can also use
order by <column number>:
order by 1;
It's a good idea to set force a new name so you know what you're getting:
(
(SELECT products.id as pid FROM products WHERE name ILIKE 'AA')
UNION
(SELECT prod_id FROM rel_cast_crew_movies WHERE cast_crew_id=1012)
)
ORDER BY pid;