Re: Extraordinary Full Join

CN:

> Even better, if possible, "sort" book and hobby column descendently to
> fill up null columns:
>
> id | name | book | hobby
> ----+------+--------+-------
> m1 | John | book1 |hobby1
> m1 | John | book2 |hobby2
>
> What is the better-have SQL to produce the last furnished list?

You'll need to add an "sort_no" column to both tbook and thobby, and ensure
somehow that both tables have the exact same sequence of sort numbers. Then
you can:

select tmaster.id, tmaster.name, tbook.book, thobby.hobby
from tmaster, tbook, thobbby
where tmaster.id = tbook.id
and tmaster.id = thobby.id
and tbook.sort_no = thobby.sort_no
order by name, tbook.sort_no

There would also be ways for you to account for having a different number of
books or hobbies, but that would be a much more complex query.

--
-Josh Berkus
Aglio Database Solutions
San Francisco