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;