ranking duplicates in table then looking up it's ID in another table

[leeclements]

Hey,

I have two tables (well, more but these two are the only ones i'm querying)

book
id | title | author | publisher | edition| ISBN (id being the primary key)

readinglist
bookID| moduleID (the 2 making a primary key)

what I would like to do is count how many times each book ID is present and return any that appear more than once (most popular 1st)and also display the book title as well, like:

bookID | title | count

At the moment I have:

SELECT bookid, count(bookid) FROM readinglist
GROUP BY bookid
HAVING COUNT(*) > 1;

which returns the book id and number of times it appears I would like to join onto it something like:

SELECT title
FROM textbook;

to get the title up. However I'm not sure how to do this. Anyone got any pointers?

(by the way I'm using oracle)

thanks in advance

Lee

[caseys_32]

First I'm assuming bookID and ID (from the book table) are the same thing. If this is correct you need only to join the tables in your query.


SELECT title, bookid, count(bookid)
FROM readinglist, book
where bookid = id
GROUP BY bookid, title
HAVING COUNT(bookid) > 1
order by count(bookid) desc;

Also you should set both table with the same field name and make the ID in the reading list table a foreign key. Hope this helps!