Problem with duplicates in a SQL Join

Question

I have the following result set from a join of three tables, an articles table, a products table, an articles to products mapping table.

I would like to have the results with duplicates removed similar to a select distinct on content id.

Current result set:

[ContendId] [Title]          [productId]
1          article one      2
1          article one      3
1          article one      9
4          article four     1
4          article four     10
4          article four     14
5          article five     1
6          article six      8
6          article six      10
6           article six      11
6          article six      13
7           article seven    14

Desired result set:

[ContendId] [Title]          [productId]
1          article one      *
4          article four     *
5          article five     *
6          article six      *
7           article seven    *

Here is condensed example of the relevant SQL:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.products') AND type = (N'U'))
drop table tempdb.dbo.products
go
CREATE TABLE tempdb.dbo.products ( 
productid int,
productname varchar(255)
)

go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articles') AND type = (N'U'))
drop table tempdb.dbo.articles
go
create table tempdb.dbo.articles (
contentid int,
title varchar(255)
)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articles') AND type = (N'U'))
drop table tempdb.dbo.articles
go
create table tempdb.dbo.articles (
contentid int,
title varchar(255)
)


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articleproducts') AND type = (N'U'))
drop table tempdb.dbo.articleproducts
go
create table tempdb.dbo.articleproducts (
contentid int,
productid int
)


insert into tempdb.dbo.products values (1,'product one'),
                                        (2,'product two'),
                                        (3,'product three'),
                                        (4,'product four'),
                                        (5,'product five'),
                                        (6,'product six'),
                                        (7,'product seven'),
                                        (8,'product eigth'),
                                        (9,'product nine'),
                                        (10,'product ten'),
                                        (11,'product eleven'),
                                        (12,'product twelve'),
                                        (13,'product thirteen'),
                                        (14,'product fourteen')

insert into tempdb.dbo.articles VALUES (1,'article one'), 
                                        (2, 'article two'), 
                                        (3, 'article three'), 
                                        (4, 'article four'), 
                                        (5, 'article five'), 
                                        (6, 'article six'), 
                                        (7, 'article seven'), 
                                        (8, 'article eight'), 
                                        (9, 'article nine'), 
                                        (10, 'article ten')


INSERT INTO tempdb.dbo.articleproducts VALUES (1,2),
                                              (1,3),
                                              (1,9),
                                              (4,1),
                                              (4,10),
                                              (4,14),
                                              (5,1),
                                              (6,8),
                                              (6,10),
                                              (6,11),
                                              (6,13),
                                              (7,14)

GO


select DISTINCT(a.contentid), a.title, p.productid  from articles a 
        JOIN articleproducts ap ON a.contentid = ap.contentid 
        JOIN products p ON a.contentid = ap.contentid AND p.productid = ap.productid 

        ORDER BY a.contentid

Answer 1

Just figured it out. I needed a proper Group By clause

select a.contentid, a.title, count(*) from articles a JOIN articleproducts ap ON a.contentid = ap.contentid JOIN products p ON a.contentid = ap.contentid AND p.productid = ap.productid Group by a.contentid, a.title ORDER BY a.contentid

Answer 2

Your question must be missing something...

SELECT DISTINCT a.*
FROM articles AS a
INNER JOIN articleproducts AS ap
  ON a.contentid = ap.contentid
ORDER BY a.contentid

Answer 3

    SELECT DISTINCT(a.contentid), a.title, p.productid  
    FROM articles a  
    INNER JOIN articleproducts ap 
         ON a.contentid = ap.contentid  
    INEER JOIN products p 
         ON a.contentid = ap.contentid 
         AND p.productid = ap.productid  
    GROUP BY (a.contentid), a.title
    ORDER BY a.contentid 

This should work!