CODEWITHSUNDEEP
sqloracle-databasejoincount
Rene Canter
Rene Canter

Reputation: 25

SQL Query needed to get information from TWO separate tables

I am trying to create a query that will list all books by the same author. Most of the list has only one book by one author, but I want the author that has multiple books listed in db to display those book for that author.

I have two tables:

I want the result to be sorted by AuthLName and the report to consist of any books in db that have same authorid.

Example result wanted:

 AUTHORID BKTITLE           AUTHFNAME     AUTHLNAME
--------- ----------------- ------------  -----------
    504   KNIGHT FREEDOM     Chris        Feehan
    504   KNIGHT SHOWDOWN    Chris        Feehan

Currently, I have the following code:

select AUTHORID, BKTITLE
from BOOK 
where AUTHORID in
    (select AUTHORID from
       (select AUTHORID, 
               count(*) as BOOK_COUNT
        from BOOK
        group by AUTHORid
        order by AUTHORid )
     where BOOK_COUNT >= 2);

which gives: 

  AUTHORID BKTITLE
---------- --------------------
       504 KNIGHT FREEDOM
       504 KNIGHT SHOWDOWN

I need to find a way to get the information from the Author Table and add it in this.

Upvotes: 2

Views: 2253

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

You can do this with only one access to each table:

SELECT * FROM
(
  SELECT AuthorID, BkTitle, AuthFName, AuthLName
        ,COUNT(*) OVER (PARTITION BY AuthorID)
         AS c
  FROM BOOK
  JOIN AUTHOR USING (AuthorID)
)
WHERE c > 1;

Upvotes: 0

Oded
Oded

Reputation: 499392

This should do:

SELECT b.AUTHORID, b.BKTITLE, a.AUTHFNAME, a.AUTHLNAME
FROM BOOK b
  INNER JOIN AUTHOR a
    ON b.AUTHORID = a.AUTHORID
AND b.AUTHORID IN
(
  SELECT AUTHORID
  FROM BOOK
  GROUP BY AUTHORID
  HAVING COUNT(AUTHORID) > 1
)
ORDER BY a.AUTHLNAME, a.AUTHFNAME

Upvotes: 2

marc_s
marc_s

Reputation: 755541

How about this - updated to use a CTE (Common Table Expression) first to figure out which authors have more than one book in the database table BOOK, and then listing only those authors and their books:

;WITH AuthorsWithMoreThanOneBook AS
(
    SELECT AUTHORID, BOOK_COUNT = COUNT(*)
    FROM BOOK
    GROUP BY AUTHORID
    HAVING BOOK_COUNT > 1)
)
SELECT
    b.AUTHORID, b.BKTITLE, a.AuthFName, a.AuthLName
FROM
    BOOK b
INNER JOIN
    AUTHOR a ON b.AuthorID = a.AuthorID
INNER JOIN 
    AuthorsWithMoreThanOneBook A2 ON a.AuthorID = A2.AuthorID
ORDER BY
    a.AuthLName, a.AuthFName

Update: OK, you're using Oracle .... not sure (haven't used it in ages) - but can't you just extend your original query something like this:

select bk.AUTHORID, bk.BKTITLE, a.AUTHORFNAME, a.AUTHORLNAME
from BOOK AS bk
INNER JOIN AUTHOR AS a ON a.AUTHORID = bk.AUTHORID
where bk.AUTHORID in
    (select AUTHORID from
       (select AUTHORID, 
               count(*) as BOOK_COUNT
        from BOOK
        group by AUTHORid
        order by AUTHORid )
     where BOOK_COUNT >= 2);

Not sure if/how Oracle supports those table aliases (BOOK AS bk) - but I'm pretty sure it does support it some way or another....

Upvotes: 1

Related Questions