Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
namrata014
Starting Member
4 Posts |
 Posted - 2009-08-12 : 10:14:39
|
| Hi all,I have 3 tables: Author, Book, Author_Book. Author and Book have a many-to-many relationship between them.Author - Author_id, Author_nameBook - Book_id, Book_nameAuthor_Book - Author_id, Book_id, Relation(Relation can be 'O', 'C' => O= Author wrote the book all by himself, 'C' = Author took assistance of other co-authors in writing the book)I need a query that returns the following:A list that has all books and the relations of each of these books with the author. Now the catch is, there could be an author who may not have any sort of a relation with a book and hence there would be no entry in the Author_Book table. But this book needs to be listed in the resultset with the author and a 'null' needs to come for the 'relation' column.Kindly help me with this. Thanks a lot in advance, |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-08-12 : 10:18:02
|
| select b.book_id,b.book_name,ab.relation,a.author_id,a.author_namefrom book bleft join author_book ab on ab.book_id = b.book_idleft join author a on a.author_id = ab.author_id |
 |
|
|
namrata014
Starting Member
4 Posts |
Posted - 2009-08-13 : 01:23:19
|
| Thanks for the quick response. I tried this but the problem is as follows:Table data:Author author_id -> 1, 2Bookbook_id -> 1, 2, 3author_book book_id -> 1, 1, 2, 3author_id -> 1, 3, 1, 2relation -> C, C , C , CNow when i try the query above, all the users get listed with their relations with the books. I need a book to be listed that could have a 'null' author_id AND a 'null' relation.Please let me know,Thanks. |
|
|
|
namrata014
Starting Member
4 Posts |
Posted - 2009-08-13 : 01:47:58
|
| Also, my most important and basic requirement is that ALL books should get listed. This is not possible by the query you suggested. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-08-13 : 02:07:12
|
| can u post required output ? |
|
|
|
namrata014
Starting Member
4 Posts |
Posted - 2009-08-13 : 02:15:53
|
| book_id author_id relation1 1 C2 null null (in case the author is not related to the book at all)3 1 C4 1 C5 null nullI have resolved this as follows for now. But I am lloking for a better way to do it.My query:(SELECT DISTINCT c.channel_id, c.channel_name, uc.relation, uc.username from channel c, user_channel uc WHERE c.channel_id NOT IN (select c.channel_id from channel c left join user_channel uc on uc.channel_id = c.channel_id left join user u on u.username = uc.username)) union (select c.channel_id, c.channel_name, uc.relation, uc.username from channel c left join user_channel uc on uc.channel_id = c.channel_id left join user u on u.username = uc.username) |
|
|
|
|
|
|
|
|
Join between 3 tables