September 12, 2014 at 5:05 am
Hi All
Hoping someone might be able to help me solve the below script. I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;
This is a snap shot of the output:
ISBN Title Publishername
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
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
Any help would be appreciated, I am new to SQL and this is driving me mad.
September 12, 2014 at 6:59 am
select b.isbn,b.title,b.publishername from book b INNER JOIN
(
SELECT title, publishername,COUNT(*) count FROM book
GROUP BY title , publishername
HAVING COUNT(*)>2
)t
on b.title=t.title and b.publishername=t.publishername
September 12, 2014 at 4:59 pm
Thank you, that worked a treat. I didn't think to do a JOIN when only working with one table.
Much appreciated.
September 12, 2014 at 11:17 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply