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

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