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.

My initial response follows:

I see two possible methods:

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

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.

—

The original poster then attempted to create a query to meet the requirements, but the query generated an error:

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

My reponse continues:

Very close to what you need. However, Oracle expects the column names to be wrapped in () … like this: where (publisher_code, type) in

The above uses a subquery, which may perform slow on some Oracle releases compared to the use of an inline view. Assume that I have a table named PART, which has columns ID, DESCRIPITION, PRODUCT_CODE, and COMMODITY_CODE, with ID as the primary key. I want to find ID, DESCRIPTION, and COMMODITY_CODE for all parts with the same DESCRIPTION and PRODUCT_CODE, where there are at least 3 matching parts in the group:

The starting point, which looks similar to your initial query:

SELECT
DESCRIPTION,
PRODUCT_CODE,
COUNT(*) NUM_MATCHES
FROM
PART
GROUP BY
DESCRIPTION,
PRODUCT_CODE
HAVING
COUNT(*)>=3;

When the original query is slid into an inline view and joined to the original table, it looks like this:

SELECT
P.ID,
P.DESCRIPTION,
P.COMMODITY_CODE
FROM
(SELECT
DESCRIPTION,
PRODUCT_CODE,
COUNT(*) NUM_MATCHES
FROM
PART
GROUP BY
DESCRIPTION,
PRODUCT_CODE
HAVING
COUNT(*)>=3) IP,
PART P
WHERE
IP.DESCRIPTION=P.DESCRIPTION
AND IP.PRODUCT_CODE=P.PRODUCT_CODE;

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: