On Mon, 22 Oct 2001, Keary Suska wrote:
> The following syntax, which works fine in 7.0.3, fails in 7.1.3:
>
> SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
> WHERE t1.col3 = t2.col4
> UNION
> SELECT t1.col1, NULL FROM table1 t1
> WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
> ORDER BY t1.col1
>
> Fails with "ERROR: Relation 't1' does not exist".
>
> It seems to be in the "order by" clause. So, if I do:
>
> SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
> WHERE t1.col3 = t2.col4
> UNION
> SELECT t1.col1, NULL FROM table1 t1
> WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
> ORDER BY table1.col1
>
> The above error goes away, but is replaced by "ERROR: ORDER BY on a
> UNION/INTERSECT/EXCEPT result must be on one of the result columns". Is this
> a bug? It doesn't even work when using full table names instead of aliases.
I think an ORDER BY col1 will do what you want, since ISTM col1 is the
column name in the query expression of the select union select.