Merging SQL query for results that are alike

Question

I have a system right now that stores products and tags that are associated with each product.

Example

Product: Microphone

Tags: Music, Electronics, Audio

There is a Tag table, Product Table, and TagProductMapping Table. The third table obviously maps the product to the tags for a one to many relation. When I query the Microphone product using a LEFT JOIN I get 3 records that are almost duplicate except the "TagName" column where obviously it has all 3 different tags. How can I merge this result? Its frustrating because if I try to query exactly 10 products, it will only limit to 10 results, which wouldnt really be 10 products.

Anyone have a good idea for this?

EDIT:

Here is the result on my query, notice how the only thing different between the 3 JobId's are the CategoryName, which are the tags.

Here are what my tables look like

-Tagmapping table:

-Tag Table

-"products table" (in this case, its my job table)

Here is my stored procedure:

ALTER PROCEDURE [dbo].[JobPostingSelectAll]                       
(                      
    @StartRowIndex     INT,                      
    @PageSize          INT,                      
    @OrderBy           VARCHAR(50),                      
    @OrderByDirection  VARCHAR(4),                      
    @CurrentUserId     INT,            
    @CategoryId     INT                      
)                      
AS                      
 SET NOCOUNT ON                                            



 SELECT         
  JobId,        
  Title,                      
  Answers,          
  UserId,                  
        UserName,        
        ProfileImageName,        
        CategoryId,        
        CategoryName,        
        Fees,            
        DESCRIPTION,                
        DateCreated,      
        UniqueTitle,    
        IsSecured                                
 FROM   (                      
            SELECT J.JobId,        
       J.Title,        
       (SELECT COUNT(ja2.JobId) FROM JobApplication ja2 left join Deliverable d2 ON d2.DeliverableId = ja2.DeliverableId
       WHERE ja2.JobId=j.JobId and (d2.PurchaseCount>0 OR d2.IsFrozen=0)) AS Answers,            
                   J.UserId,        
                   U.UserName,        
                   U.ImageName as ProfileImageName,        
                   J.CategoryId,        
                   C.CategoryName,        
                   J.Fees,        
                   J.Description,                                              
                   J.DateCreated,      
                   J.UniqueTitle,    
                   J.IsSecured,                                                
                   ROW_NUMBER() OVER(                      
                       ORDER BY                  
                       CASE                       
                            WHEN @OrderByDirection = 'asc' AND @OrderBy = 'Answers'             
                            THEN (SELECT COUNT(ja2.JobId) FROM JobApplication ja2 left join Deliverable d2 ON d2.DeliverableId = ja2.DeliverableId
       WHERE ja2.JobId=j.JobId and (d2.PurchaseCount>0 OR d2.IsFrozen=0)) END ASC,            
                       CASE                       
                            WHEN @OrderByDirection = 'asc' AND @OrderBy = 'Answers'             
                            THEN J.DateCreated END DESC,            
                       CASE                       
                            WHEN @OrderByDirection = 'asc' AND @OrderBy = 'Answers'             
                            THEN J.Title END ASC,            
                       CASE                       
                            WHEN @OrderByDirection = 'desc' AND @OrderBy = 'Answers'             
                            THEN (SELECT COUNT(ja2.JobId) FROM JobApplication ja2 left join Deliverable d2 ON d2.DeliverableId = ja2.DeliverableId
       WHERE ja2.JobId=j.JobId and (d2.PurchaseCount>0 OR d2.IsFrozen=0)) END DESC,                                             
      CASE                       
                            WHEN @OrderByDirection = 'desc' AND @OrderBy = 'Answers'             
                            THEN J.DateCreated END DESC,            
                         CASE                       
                            WHEN @OrderByDirection = 'desc' AND @OrderBy = 'Answers'             
                            THEN J.Title END ASC,            
                       CASE WHEN @OrderByDirection = 'asc' AND @OrderBy = 'Fees'             
       THEN J.Fees END ASC,            
       CASE WHEN @OrderByDirection = 'asc' AND @OrderBy = 'Fees'             
       THEN J.DateCreated END DESC,            
        CASE WHEN @OrderByDirection = 'desc' AND @OrderBy = 'Fees'             
       THEN J.Fees END DESC,            
                       CASE WHEN @OrderByDirection = 'desc' AND @OrderBy = 'Fees'             
       THEN J.DateCreated END DESC,            
                       CASE WHEN @OrderByDirection = 'asc' AND @OrderBy = 'DateCreated'             
       THEN J.DateCreated END ASC,                      
                       CASE WHEN @OrderByDirection = 'desc' AND @OrderBy = 'DateCreated'             
       THEN J.DateCreated END DESC            
                   ) AS RowIndex           
            FROM   [JobPosting] J    
                LEFT JOIN TagMapping TM ON J.JobId = TM.QuestionId      
                        LEFT JOIN Categories C ON TM.TagId = C.CategoryID            
            Left Join [User] U ON J.UserId = U.UserID            
            WHERE  J.IsLocked = 0  AND j.IsDeleted = 0                                           
       AND (@CategoryId IS NULL OR  J.CategoryId = @CategoryId)            

        ) AS JobPostingList                      
 WHERE  RowIndex BETWEEN @StartRowIndex AND (@StartRowIndex + @PageSize) - 1             


 SELECT COUNT(J.JobID) AS TotalRecords                      
 FROM   JobPosting J                                      
 WHERE  J.IsLocked = 0 AND J.IsDeleted = 0                        
        AND (@CategoryId IS NULL OR  J.CategoryId = @CategoryId)          


-- select all filecount grouped by Type Of File for specific Job                
SELECT J.JobId, F.MimeType, COUNT(F.FileId) AS FileCount                
FROM                   
JobPosting J  
Left Join Files F ON  F.JobPostingId = J.JobId  
WHERE  J.IsLocked = 0 AND J.IsDeleted = 0                        
        AND (@CategoryId IS NULL OR  J.CategoryId = @CategoryId)          
GROUP BY                                
       F.MimeType,J.JobId  
Having COUNT(F.FileId) > 0

Answer 1

The issue you're having occurs because your database structure normalizes tag data for each product (I've found this page to be a good reference).

When you SELECT from your Products table and JOIN over to your Tags table, it's crucial to remember that you're not getting a list of Products; rather, you're getting a listing of Product-Tag combinations.

If you want to get a list of the top 10 products along with their tag information, I'd suggest using a subquery:

select * from 
    (select top 10 * from ProductsTable) TopProducts
    inner join Tagmapping on TopProducts.ProductID = Tagmapping.ProductID
    inner join Tags on Tagmapping.TagID = Tags.TagID

Even though this solves your initial selection issue, this will still produce the kind of multiple listing you show above where only the tag information differs from row to row.

It's possible to format the output to have multiple tag entries (maybe separated by commas) as described here, but that's going to be something you want to do as a last step before you present the data to the user, either through SQL or whatever software layer you're using as a go-between.

Answer 2

I believe that if you are not interested in the tag name, you can select all columns except the tag name and use "select distinct" to show just the products without its tags. I can be wrong though =(