| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with Query
"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message
news:47499F3F.1080209_at_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 >
>
>
> 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
>
>
>
>
Hi Maxim,
Thank you very much this is very close to what I am looking for. I was thinking that I needed to do a self join on the table somehow just was not sure how to do it. I like the way that your table was listed:
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
Mine did not come out listed that away? here is how mine came out what do I need to do to get it to look like your table?
SQL> select a.publisher_code,a.type,a.title,b.title
2 from book a, book b 3 where a.publisher_code=b.publisher_code 4 and a.type=b.type 5 and a.title<b.title 6 order by a.type, a.publisher_code;
PU TYP TITLE
-- --- ----------------------------------------TITLE
LB FIC Franny and Zooey
The Catcher in the Rye
LB FIC Nine Stories
The Catcher in the Rye
PU TYP TITLE
-- --- ----------------------------------------TITLE
PE FIC East of Eden
The Grapes of Wrath
PE FIC Of Mice and Men
The Grapes of Wrath
PU TYP TITLE
-- --- ----------------------------------------TITLE
PL FIC Jazz
Song of Solomon
PL FIC Beloved
Song of Solomon
PU TYP TITLE
-- --- ----------------------------------------TITLE
JP MYS Slay Ride
The Edge
ST SFI Harry Potter and the Goblet of Fire Harry Potter and the Prisoner of Azkaban
12 rows selected.
Thanks again for all the help from everyone.
Jeff Received on Sun Nov 25 2007 - 11:08:04 CST
 |
 |