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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly