Re: Extraordinary Full Join

Thank you! Josh,

> 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.

Perhaps I did not make my requirement clear. My ultimate goal is to

(1) list not null values first in columns book and hobby
(2) list book and hobby in either descending or ascending order

For example, improving

id | name | book | hobby
---+------+--------+-------
m1 | John | |hobby1
m1 | John | book2 |
m1 | John | |hobby2
m1 | John | book1 |hobby3

to gain (for descending)

id | name | book | hobby
---+------+--------+-------
m1 | John | book2 |hobby3
m1 | John | book1 |hobby2
m1 | John | |hobby1

or gain (for ascending)

id | name | book | hobby
---+------+--------+-------
m1 | John | book1 |hobby1
m1 | John | book2 |hobby2
m1 | John | |hobby3

I don't want to add "sort_to" column to any table because I have more
tables similar to book and hobby. For example,

CREATE TABLE tfriend(id TEXT, friend TEXT);
CREATE TABLE tfriend(id TEXT, education TEXT);

and hopefully to get

id | name | book | hobby | friend | education
---+------+--------+-------+---------+-----------
m1 | John | book2 |hobby3 | FriendA | Edu C
m1 | John | book1 |hobby2 | | Edu B
m1 | John | |hobby1 | | Edu A

Do I have any chance to achieve the ultimate goal?
I need, at least, the correct result for the worst case like:

id | name | book | hobby
---+------+--------+-------
m1 | John | book1 |
m1 | John | | hobby2
m1 | John | book2 |
m1 | John | | hobby1

Regards,

CN

--
http://www.fastmail.fm - Choose from over 50 domains or use your own