CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2012sql-server-2014
perkes456
perkes456

Reputation: 1181

Removing duplicates from SQL query in One to many relationship

I have a table relationship as following:

Table 1 -> Table 2 (one to many relationship in DB)

If I do the following query on table 1:

select *
from table1 as t1
where t1.id = 1

I will get only one record for a specific record from that table, but if I do join on the table 2 as following:

    select *
    from table1 as t1
    join table2 as t2
    on t1.id = t2.id
    where t1.id=1

This time I will get multiple records if I have ID from table 1 inserted multiple times in table 2. The question is now, is there any way to show just 1 record after doing this join in query, in one to many relationship??

Can someone help me out with this?? Thanks!

I have used koppinjo's way and the query now looks as following:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY PC.SubCategoryID 
                                     ORDER BY PC.[SubCategoryID] ) AS [Row]
                   ,sc.*
                   ,pc.MeasurementQuantity
                   ,pc.Price
                   ,pc.ProductCategoryID
                   ,pc.ProductID
                   ,p.Dimensions
                   ,p.FileName
                   ,p.ProductDescription
                   ,p.ProductName
                   ,mu.Unit
          FROM      SubCategory AS sc
                    JOIN ProductsCategories AS pc ON sc.SubCategoryID = pc.SubCategoryID
                    JOIN Products AS p ON p.ProductID = pc.ProductID
                    JOIN MeasurementUnits AS mu 
                          ON mu.MeasurementUnitID = p.MeasurementUnitID
          WHERE     pc.SubCategoryID = 1
        ) AS t
WHERE   t.[Row] = 1

The problem is now, the query returns only 1 result. But what if there are two products that are under the same category ??

P.S. I forgot to mention that the actual DB schema for this query is:

Products -> ProductsCategories <- Categories

So lets say that 2 products are under two categories, i need to get both of the products, but without duplicates. Koppinjo's way returns only 1 result...

Upvotes: 1

Views: 3070

Answers (2)

Ramon
Ramon

Reputation: 3

You can use distinct and don't show any columns from table2. Something like:

select distinct t1.*
from table1 as t1
join table2 as t2
on t1.id = t2.id
where t1.id=1

Upvotes: 0

How &#39;bout a Fresca
How &#39;bout a Fresca

Reputation: 2317

I would suggest using a window function like ROW_NUMBER(). For instance:

SELECT *
FROM (
      SELECT
          ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.[primary/unique key]) AS [row]
         ,t1.*
      FROM table1 t1
          JOIN table2 t2 ON t1.id = t2.id
      WHERE t1.id = 1
     ) t
WHERE t.[row] = 1

Something like this should get you pointed in the right direction. Hope it helps!

Upvotes: 3

Related Questions