Help with MS SQL query on duplicates

fabiano petrone
fabiano petrone used Ask the Experts™
on
Hello,
excuse the (probably) newbie question.
I must discover the "duplicates" in a list of books (where "duplicates" means books of same title and same location).
I've created the following query based on the items table of the database:

SELECT [bookId]
      ,[locationId]
	  , COUNT(*)
  FROM [database].[dbo].[items]
  GROUP BY
    [bookId], [locationId]
HAVING
    COUNT(*) > 1

Open in new window

     
it works well, but the info about the books is completed by other 2 tables:

1) [database].[dbo].[books]
that has [Id] = [database].[dbo].[items].[bookId]

2) [database].[dbo].[locations]
that has [Id] = [database].[dbo].[items].[locationId]

these tables contains other useful info that I want to include in my previous query, like:
[database].[dbo].[books].[title]
[database].[dbo].[locations].[location_name]
is it possible doing so?
Thanks a lot,
Fabiano
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Hope it is not a homework question..
Kindly have a look at JOINS..
https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15
http://www.sqlservertutorial.net/sql-server-basics/sql-server-inner-join/
  SELECT b.title
      ,l.location_name
	  , COUNT(*)
  FROM [database].[dbo].[items] i
  JOIN [database].[dbo].[books] b on b.Id = i.bookId
  JOIN [database].[dbo].[locations] l on l.Id = i.locationId
  GROUP BY b.title
      ,l.location_name
HAVING
    COUNT(*) > 1

Open in new window

Partha MandayamTechnical Director
Commented:
select b.title, l.location_name
from
(SELECT [bookId]
      ,[locationId]
        , COUNT(*)
  FROM [database].[dbo].[items]
  GROUP BY
    [bookId], [locationId]
HAVING
    COUNT(*) > 1) dup
join books b on b.bookid=dup.bookid
join locations l on l.locationid=dup.locationid

Author

Commented:
Hi, Raja and Partha
Thanks a lot for your help!
I've marked both as solutions.
Take Care,
Fabiano

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial