How to handle One-to-Many Join without repeat rows

Question

In MS SQL Server I have two tables [Products] and [Product_img] with relationship (ONE TO MANY).

SELECT p_id, name FROM dbo.products

| 15 | | name1 |

| 17 | | name2 |

SELECT p_id, path FROM dbo.product_img

| 1.jpg | | 15 |

| 2.jpg | | 15 |

| 3.jpg| | 17 |

| 4.jpg | | 17 |

SELECT dbo.product_img.p_id, dbo.products.name, dbo.product_img.path FROM dbo.product_img INNER JOIN dbo.products ON dbo.product_img.p_id = dbo.products.p_id

| 15 | | name1 | | 1.jpg |

| 15 | | name1 | | 2.jpg |

| 17 | | name2 | | 3.jpg |

| 17 | | name2 | | 4.jpg |

What is the SQL statement that can show the tables like this (with out repeating)?

| 15 | | name1 | | 1.jpg | | 2.jpg |

| 17 | | name2 | | 3.jpg | | 4.jpg |

score 4 · Accepted Answer · answered Apr 21 '11 at 19:50

4

USE MAX AND MIN

SELECT
  dbo.product_img.p_id
, dbo.products.name
, MAX(dbo.product_img.path)
, MIN(dbo.product_img.path) 
FROM dbo.product_img 
INNER JOIN dbo.products 
    ON dbo.product_img.p_id = dbo.products.p_id
 GROUP BY dbo.product_img.p_id , dbo.products.name

answered Apr 21 '11 at 19:50

Robbie Tapping

2,516
1
17
18

No problems at all, just be weary though that if you have more than one 2 images you are going to have problems, so i would strongly avoid structuring your table like the way you have wanted to – Robbie Tapping Apr 21 '11 at 20:06

score 4 · Answer 2 · answered Apr 21 '11 at 20:12

If you can have more than two images per product you can do this to get one column with the img path's.

select
  p.p_id,
  p.name,
  stuff((select ', '+i.path
         from product_img as i 
         where p.p_id = i.p_id
         for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') as path  
from products as p

Result

p_id    name    path
-----   -----   ------------
15      name1   1.jpg, 2.jpg
17      name2   3.jpg, 4.jpg

How to handle One-to-Many Join without repeat rows

2 Answers2