| Author |
Join syntax question
|
|
| danlamb 2005-06-15, 7:23 am |
| Consider this simplified table structure:
[Books]
ID
TITLE
[Authors]
ID
BOOKID
NAME
The problem: Books may have no authors, one author or mulitple
authors. I need a query that shows a list of books and only one author
(doesn't matter which one) or NULL if no authors exist, each book can
only be listed once.
Any input would be greatly appreciated.
Thanks,
Dan Lamb
lamb dot dan at gmail dot com
| |
| Barbara Boehmer 2005-06-15, 9:23 am |
| scott@ORA92> SELECT * FROM books
2 /
ID TITLE
---------- ----------
1 title1
2 title2
3 title3
scott@ORA92> SELECT * FROM authors
2 /
ID BOOKID NAME
---------- ---------- ----------
10 2 name2
20 3 name3
20 3 name4
scott@ORA92> SET NULL NULL
scott@ORA92> SELECT b.title AS book_title, MIN (a.name) AS author
2 FROM books b, authors a
3 WHERE b.id = a.bookid (+)
4 GROUP BY b.title
5 /
BOOK_TITLE AUTHOR
---------- ----------
title1 NULL
title2 name2
title3 name3
scott@ORA92>
| |
| Bagieta 2005-06-15, 9:23 am |
| select b.*,
(select name from authors a where a.bookid = b.id and rownum = 1)
from books b
--
Kind regards
Bagieta
------------------------------------
dbDeveloper - Oracle database editor
www.dbdeveloper.prominentus.com
------------------------------------
| |
| Andreas Mosmann 2005-06-15, 9:23 am |
| danlamb schrieb am 15.06.2005 in
<1118837603.072821.131140@g14g2000cwa.googlegroups.com>:
> Consider this simplified table structure:
> [Books]
> ID
> TITLE
> [Authors]
> ID
> BOOKID
> NAME
It makes no problem for you, that 1 author can write only on max 1 book?
Andreas Mosmann
--
wenn email, dann AndreasMosmann <bei> web <punkt> de
| |
| danlamb 2005-06-15, 9:23 am |
| Thanks for the quick response!
| |
| danlamb 2005-06-15, 9:23 am |
| Thanks for the kind help. Problem solved!
| |
| danlamb 2005-06-15, 9:23 am |
| Thank you everyone! Your solutions worked great!
|
|
|
|