At 07:42 PM 4/02/2004 +0000, you wrote:

>Firstly, thanks in particular to Helen Borrie and Thomas Miller. Both
>your replies have been very very useful indeed (though it did take me
>a few minutes to figure out how to input the UDF's into the db - I
>though it would be available from the server side, doh!)
>
>select
>b1.book_id,
>CAST(0 AS DOUBLE PRECISION) as stock_id,
>b1.price,
>string2blob('') as condition from Book b1
>UNION
>select
>bk.book_id,
>sk.stock_id,
>sk.price,
>sk.condition
>from Book bk
>join Stock sk on bk.book_id = sk.book_id
>
>
>Anyway, the join now works quite well.
>
>I just wanted to ask one final question that would put the icing on
>this cake (I can't get it to work and I'm not sure if it can be done).
>
>I wish the resultant result set to be grouped by the book_id. Putting
>in "group by b1.book_id" or "group by bk.book_id" does not seem to
>work. I even changed the alias in the first select statement from b1
>to bk.
>
>Obviously this needs a more intelligence than I can muster.

No, you can't group UNION output directly.
-- You can form groups within the individual unioned sets.
-- You can make a UNION query into a view and use grouping in queries on
the view.

That said, any grouping on book_id in this set would not be able to output
anything except the book_id itself and the results of aggregating
expressions, e.g. SUM( ), AVG( ), COUNT( ), MAX( ) or MIN( ).

Do you understand the difference between grouping and ordering in SQL? If
what you really want is ordering, then place the ORDER BY clause absolutely
last, after all of the SELECT specifications, and use the column numbers as
arguments.

/hb