Quick thought, there is no need for a self-join as this is an elementary set problem which is easily solved using the window functions in SQL Server 2005 or later.
USE tempdb;
GO
SET NOCOUNT ON;
/* Sample data in a consumable format
*/
;WITH BOOK_SAMPLE (ISBN, Title, Publishername) AS
( SELECT * FROM (VALUES
('0-1311804-3-6' , 'C' ,'Prentice Hall' )
,('* 0-0788132-1-2', 'C' ,'OSBORNE MCGRAW-HILL')
,('* 0-0788153-8-X', 'C' ,'OSBORNE MCGRAW-HILL')
,('* 0-9435183-3-4', 'C Database Development' ,'MIS' )
,('* 1-5582806-2-6', 'C Database Development' ,'MIS' )
,('* 1-5582813-6-3', 'C Database Development' ,'MIS' )
,('* 0-0788165-4-8', 'C++' ,'OSBORNE MCGRAW-HILL')
,('9-9913649-9-4' , 'C++' ,'Prentice Hall' )
,('* 9-9922679-5-X', 'C++' ,'OSBORNE MCGRAW-HILL')
,('* 0-0788164-9-1', 'Clipper Programming' ,'OSBORNE MCGRAW-HILL')
,('* 0-0788175-8-7', 'Clipper Programming' ,'OSBORNE MCGRAW-HILL')
,('0-8306354-2-4' , 'Clipper Programming' ,'Windcrest' )
,('0-8306854-2-1' , 'Clipper Programming' ,'Windcrest' )
,('* 9-9911163-6-2', 'Clipper Programming' ,'OSBORNE MCGRAW-HILL')
) AS X(ISBN, Title, Publishername)
)
/* CTE COUNTING_TITLE_PUBLISHER
Counts the instances of Title and Publishername by
partitioning the set on those two columns. In addition
it has a row_number function with the same partition
clause to identify each instance within the group.
*/
,COUNTING_TITLE_PUBLISHER AS
(
SELECT
BS.ISBN
,BS.Title
,BS.Publishername
,COUNT(BS.ISBN) OVER
(
PARTITION BY BS.Title
,BS.Publishername
) AS TITPUB_COUNT
,ROW_NUMBER() OVER
(
PARTITION BY BS.Title
,BS.Publishername
ORDER BY BS.ISBN
) AS TITPUB_RID
FROM BOOK_SAMPLE BS
)
/* The set for the CTE COUNTING_TITLE_PUBLISHER is
then filtered to produce the desired results.
*/
SELECT
CTP.ISBN
,CTP.Title
,CTP.Publishername
,CTP.TITPUB_COUNT
,CTP.TITPUB_RID
FROM COUNTING_TITLE_PUBLICHER CTP
WHERE CTP.TITPUB_COUNT > 2;
Results
ISBN Title Publishername TITPUB_COUNT TITPUB_RID
--------------- ---------------------- ------------------- ------------ -----------
* 0-9435183-3-4 C Database Development MIS 3 1
* 1-5582806-2-6 C Database Development MIS 3 2
* 1-5582813-6-3 C Database Development MIS 3 3
* 0-0788164-9-1 Clipper Programming OSBORNE MCGRAW-HILL 3 1
* 0-0788175-8-7 Clipper Programming OSBORNE MCGRAW-HILL 3 2
* 9-9911163-6-2 Clipper Programming OSBORNE MCGRAW-HILL 3 3
Edit:type