CODEWITHSUNDEEP
sql-serverstored-procedures
Hossein Dahr
Hossein Dahr

Reputation: 75

How to distinct duplicated records in sql?

Hi I have two tables named ArtPlace and PhotoTable. i store places in artPlace table and their photos to photoTable and i pass Id of place as an ObjectId to PhotoTable.for any image that i record for a place it adds record with same ObjectId .when i want to retrieve the art place i want to get just one record; here is my code sample and the result:

SELECT ArtPlace.Id ,
       PhotoTable.ObjectId,
       ArtPlace.ArtPlaceName,
       ArtPlace.Address,
       ArtPlace.Title,
       ArtPlace.Summary,
       ArtPlace.Description,
       ArtPlace.Visitor,
       ArtPlace.Website,
       PhotoTable.PhotoName
FROM ArtPlace
INNER JOIN City ON ArtPlace.CityId = City.Id
INNER JOIN PhotoTable ON( dbo.PhotoTable.ObjectId = dbo.ArtPlace.Id and dbo.PhotoTable.CategoryId = 5 )
WHERE ArtPlace.CityId = @CityId AND 
      dbo.ArtPlace.IsActive = 1 AND 
      dbo.PhotoTable.CategoryId = 5

END;

here is the result: please click on it to see the only difference is in just photoName colunm

Upvotes: 0

Views: 42

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You query giving you correct result but if you want only one record then you can use ROW_NUMBER

;WITH CTE AS (
       SELECT 
         ROW_NUMBER() OVER(PARTITION BY ArtPlace.Id ORDER BY ArtPlace.Id) RN,
         ArtPlace.Id ,
         PhotoTable.ObjectId,
         ArtPlace.ArtPlaceName,
         ArtPlace.Address,
         ArtPlace.Title,
         ArtPlace.Summary,
         ArtPlace.Description,
         ArtPlace.Visitor,
         ArtPlace.Website,
         PhotoTable.PhotoName
     FROM ArtPlace
     INNER JOIN City ON ArtPlace.CityId = City.Id
     INNER JOIN PhotoTable ON dbo.PhotoTable.ObjectId = dbo.ArtPlace.Id
     WHERE ArtPlace.CityId = @CityId AND 
     dbo.ArtPlace.IsActive = 1 AND 
     dbo.PhotoTable.CategoryId = 5
  )
  SELECT * 
  FROM CTE
  WHERE RN=1

Upvotes: 1

Sebz
Sebz

Reputation: 502

I think the issue here is the JOIN with the PhotoTable, because for each picture in that location it returns the rows with the location detail. My suggestion is to replace the join with an EXISTS clause and remove the PhotoTable.PhotoName and PhotoTable.ObjectId from the result set. This way you are looking at the locations only without any Photo details.

SELECT ArtPlace.Id ,
        ArtPlace.ArtPlaceName,
        ArtPlace.Address,
        ArtPlace.Title,
        ArtPlace.Summary,
        ArtPlace.Description,
        ArtPlace.Visitor,
        ArtPlace.Website
 FROM ArtPlace
      INNER JOIN City ON ArtPlace.CityId = City.Id
 WHERE ArtPlace.CityId = @CityId
       AND dbo.ArtPlace.IsActive = 1
       AND EXISTS(SELECT 1 FROM PhotoTable WHERE dbo.PhotoTable.ObjectId = dbo.ArtPlace.Id and dbo.PhotoTable.CategoryId = 5 )

Also I would suggest to remove the hardcoding of CategoryId = 5 and make it a param with a default value if it's not possible to have it a pure parameter.

Upvotes: 1

Related Questions