Re: Help with Query

On Nov 25, 9:52 am, "Jeff B" <jef..._at_KnoSpam.tds.net> wrote:
> Hi everyone,
>
> I have a book table and in that table it has the book tile, publisher, and
> type of book it is. example mystery, scifi, etc...
>
> I am trying to write a query that brings back a list of every pair of books
> that have the same publisher and same book type. I have been able to get
> the following code to work:
>
> select publisher_code, type
> from book
> group by publisher_code, type
> having count(*) > 1;
>
> which returns the following results:
>
> PU TYP
> -- ---
> JP MYS
> LB FIC
> PE FIC
> PL FIC
> ST SFI
> VB FIC
>
> I can not figure out how to get the book title and book code for the books
> that this result list represents, everything i have tried throws out an
> error.
>
> Can someone help?
>
> Thanks,
>
> Jeff

I see two possible methods:
1. Slide the SQL statement that you have written into an inline view, join the inline view to your book table, and then use the publisher_code, type columns to drive back into your book table. The join syntax may look like one of the following: (publisher_code, type) IN (SELECT...) or b.publisher_code=ib.publisher_code and b.type=ib.type
2. Use analytical functions (COUNT() OVER...) to determine the number of matches for the same publisher_code, type columns. Then slide this SQL statement into an inline view to retrieve only those records with the aliased COUNT() OVER greater than 1. This has the benefit of retrieving the matching rows in a single pass.

You will likely find examples of the above approaches in this and the comp.databases.oracle.server group.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Nov 25 2007 - 09:28:47 CST