CODEWITHSUNDEEP
sqloracle-database
Zedrick Lloyd Tenorio
Zedrick Lloyd Tenorio

Reputation: 39

How can we retrieve same data in one table?

I need one very expert help, I can't seem to find the answer to this, the problem is, find the titles and ISBNs of books that have the same title and publishers as at least 2 other books.

Order it by title.

So basically, everything is coming from the book table. 

CREATE TABLE BOOK 
(
   ISBN VARCHAR2(20),
   Title VARCHAR2(250) NOT NULL,
   YearPublished NUMBER(4) NOT NULL,
   PublisherName VARCHAR2(40) NOT NULL,

   CONSTRAINT pk_isbnP PRIMARY KEY(ISBN)
);

Here is my rough draft: 

select _____
from book b2
where ______ (select____ from book b2
             where _____ = ______ and ______ =______)

Upvotes: 1

Views: 101

Answers (4)

Satyajit
Satyajit

Reputation: 2210

You can use group by statement here.

SELECT title
      ,isbn
  FROM BOOKS
  group by title, isbn
  having COUNT(1) >= 2 order by title;

Upvotes: 0

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

This ought to do it:

select isbn, title
from books
where (title,publishername) in(select title,publishername 
                                 from books
                                 group by title,publishername
                                 having count(*) >=3)
order by title;

Upvotes: 0

Bacs
Bacs

Reputation: 919

select b1.title, b1.isbn 
from book b1 
inner join 
  (select title, publishername 
   from book
   group by title, publishername having count(*) > 2) b2 
on b1.title = b2.title and b1.publishername = b2.publishername
order by b1.title

The inner query fetches the titles/publishers of books where there are three or more duplicates. The outer query uses those results to get the associated ISBNs.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Step 1: Find combinations of title and publisher that have at least 2 books:

SELECT title, PublisherName 
FROM BOOK
GROUP BY title, PublisherName 
HAVING COUNT(*) > 1

Step 2: find all other books that have this title and publisher:

SELECT *
FROM Books b1
WHERE EXISTS(
    SELECT title, PublisherName 
    FROM BOOK b2
    WHERE b1.title = b2.title AND b1.PublisherName = b2.PublisherName 
    GROUP BY title, PublisherName 
    HAVING COUNT(*) > 1
)

Upvotes: 1

Related Questions