Re: how to do this "simple yet hard" query in Oracle9

"teri" <teriashahost_at_hotmail.com> wrote in message news:c2f0e492.0204200522.72e36448_at_posting.google.com...
> I know it's probably very easy once you've done similar queries
> before, but I'm no pro at this so have me excused...
>
> I have three tables: a author, b book and c publisher.
> An author can write many books, each book may have a different
> publisher, you get the idea:
>
> a author (a-key)
> b book (b-key c-key)
> c publisher (c-key)
>
> I want to submit a query that will get only the authors that have
> written all their books for the same publisher.
> Only those instances of a that may be assosciated with different
> instances of b but just one c (there is no direct key between a and c
> of course)?
>
> I'd be grateful if anyone has an answer.
>
>
>
> (If this message didn't fit into the subject of group I apologize)

Assuming there is an a-key in book (there should be, otherwise the relationship wouldn't hold

select a-key
from book b
group by a-key
having count(distinct c-key) = 1

will result in the authors with only one publisher You can join this with the author table.

One small piec of advice:
I'm not sure whether this design is supposed to act as a real world system. However, in a real world system, a book definitely can have multiple authors and also definitely multiple, international, publishers.

--
Regards


--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address