Re: missing FROM-clause notice but nothing is missing ...

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.