Re: Help with Query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 25 Nov 2007 17:13:51 +0100
Message-ID: <47499F3F.1080209@gmail.com>

Jeff B schrieb:
> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
> news:4ed69188-f428-46dd-86f0-8850efb9faa9_at_n20g2000hsh.googlegroups.com...

>> 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.

>
> Hi Charles,
>
> Thanks for the response. I can not say that I understand everything
> that you were saying. When you say inline view do you mean like this?
>
> select title
> from book
> where publisher_code and type in
> (select publisher_code, type
> from book
> group by publisher_code, type
> having count(*) > 1);
>
> this did not work it threw back the following error:
>
> SQL> select title
> 2 from book
> 3 where type in
> 4 (select publisher_code, type
> 5 from book
> 6 group by publisher_code, type
> 7 having count(*) > 1);
> (select publisher_code, type
> *
> ERROR at line 4:
> ORA-00913: too many values
>
>
> SQL> select title
> 2 from book
> 3 where publisher_code, type in
> 4 (select publisher_code, type
> 5 from book
> 6 group by publisher_code, type
> 7 having count(*) > 1);
> where publisher_code, type in
> *
> ERROR at line 3:
> ORA-00920: invalid relational operator
>
>
> SQL> select title
> 2 from book
> 3 where publisher_code and type in
> 4 (select publisher_code, type
> 5 from book
> 6 group by publisher_code, type
> 7 having count(*) > 1);
> where publisher_code and type in
> *
> ERROR at line 3:
> ORA-00920: invalid relational operator
>
> is this what you were referring too? still not being able to get it. I
> am thinking that i have to be half way to the solution with the first
> part that did bring back the six sets, just cannot figure out how to get
> the book code and titles for each of those six sets?
>
> Again thanks for the help
>
> Jeff

You want get all pairs of books from your table - to get all permutations usually cross join is used. If you consider (1,2) to be the same as (2,1), then all permutations should be reduced about a half - to achieve it, a simple condition like (j<k) - where j,k are your set items

is enough. To get your selection restricted only on the same type and publisher - simply put this restriction into the join condition. Finally, you can get something like:

SQL> with books as (

2 select 'JP' publisher_code,'MYS' type,'The Jupiter Legacy' title from dual union all

3 select 'ST','SFI','The Daleth Effect' from dual union all 4 select 'ST','SFI','A Stainless Steel Rat is Born' from dual union all

   5   select 'JP','MYS','Galactic Dreams' from dual     union all
   6   select 'VB','MYS','The Puppet Masters' from dual  union all
   7   select 'LB','FIC','The Stainless Steel Rat' from dual     union all
   8   select 'PE','FIC','One Step from Earth' from dual  union all
   9   select 'PL','FIC','Planet of No Return' from dual  union all
  10   select 'VB','FIC','The Technicolor Time Machine' from dual  union all
  11   select 'LB','FIC','A Rebel in Time' from dual     union all
  12   select 'PE','FIC','Skyfall' from dual     union all
  13   select 'PL','FIC','War With the Robots' from dual  union all
  14   select 'VB','FIC','Stainless Steel Visions' from dual     union all
  15   select 'ST','FIC','The Menace from Earth' from dual

  16 )
  17 -- End test data
  18 select a.publisher_code,a.type,a.title,b.title   19 from books a,books b
  20 where a.publisher_code=b.publisher_code   21 and a.type=b.type
  22 and a.title<b.title
  23 /

PU TYP TITLE                         TITLE
-- --- ----------------------------- -----------------------------
JP MYS Galactic Dreams               The Jupiter Legacy
ST SFI A Stainless Steel Rat is Born The Daleth Effect
LB FIC A Rebel in Time               The Stainless Steel Rat
VB FIC Stainless Steel Visions       The Technicolor Time Machine
PE FIC One Step from Earth           Skyfall
PL FIC Planet of No Return           War With the Robots

6 rows selected.

If you like to have all your titles in one column, then it'll look like

select a.publisher_code,a.type,a.title from books a,books b
where a.publisher_code=b.publisher_code and a.type=b.type
-- and a.title<b.title

Best regards

Maxim Received on Sun Nov 25 2007 - 10:13:51 CST