Left Join without duplicate rows from left table

Question

Please look at the following query:

tbl_Contents

Content_Id  Content_Title    Content_Text 10002   New case Study   New case Study 10003   New case Study   New case Study 10004   New case Study   New case Study 10005   New case Study   New case Study 10006   New case Study   New case Study 10007   New case Study   New case Study 10008   New case Study   New case Study 10009   New case Study   New case Study 10010   SEO News Title   SEO News Text 10011   SEO News Title   SEO News Text 10012   Publish Contents SEO News Text 

tbl_Media

Media_Id    Media_Title  Content_Id 1000    New case Study   10012 1001    SEO News Title   10010 1002    SEO News Title   10011 1003    Publish Contents 10012 

QUERY

SELECT  C.Content_ID, C.Content_Title, M.Media_Id  FROM tbl_Contents C LEFT JOIN tbl_Media M ON M.Content_Id = C.Content_Id  ORDER BY C.Content_DatePublished ASC 

RESULT

10002   New case Study  2014-03-31 13:39:29.280 NULL 10003   New case Study  2014-03-31 14:23:06.727 NULL 10004   New case Study  2014-03-31 14:25:53.143 NULL 10005   New case Study  2014-03-31 14:26:06.993 NULL 10006   New case Study  2014-03-31 14:30:18.153 NULL 10007   New case Study  2014-03-31 14:30:42.513 NULL 10008   New case Study  2014-03-31 14:31:56.830 NULL 10009   New case Study  2014-03-31 14:35:18.040 NULL 10010   SEO News Title  2014-03-31 15:22:15.983 1001 10011   SEO News Title  2014-03-31 15:22:30.333 1002 10012   Publish         2014-03-31 15:25:11.753 1000 10012   Publish         2014-03-31 15:25:11.753 1003 

10012 are coming twice...!

My query is returning duplicate rows from tbl_Contents (left table in the join)

Some rows in tbl_Contents has more than 1 associated rows in tbl_Media. I need all rows from tbl_Contents even if there are Null values exists in the tbl_Media BUT NO DUPLICATE RECORDS.

Answer 1

Try an OUTER APPLY

SELECT      C.Content_ID,     C.Content_Title,     C.Content_DatePublished,     M.Media_Id FROM      tbl_Contents C     OUTER APPLY     (         SELECT TOP 1 *         FROM tbl_Media M          WHERE M.Content_Id = C.Content_Id      ) m ORDER BY      C.Content_DatePublished ASC 

Alternatively, you could GROUP BY the results

SELECT      C.Content_ID,     C.Content_Title,     C.Content_DatePublished,     M.Media_Id FROM      tbl_Contents C     LEFT OUTER JOIN tbl_Media M ON M.Content_Id = C.Content_Id  GROUP BY     C.Content_ID,     C.Content_Title,     C.Content_DatePublished,     M.Media_Id ORDER BY     C.Content_DatePublished ASC 

The OUTER APPLY selects a single row (or none) that matches each row from the left table.

The GROUP BY performs the entire join, but then collapses the final result rows on the provided columns.

Answer 2

You can do this using generic SQL with group by:

SELECT C.Content_ID, C.Content_Title, MAX(M.Media_Id) FROM tbl_Contents C LEFT JOIN      tbl_Media M      ON M.Content_Id = C.Content_Id  GROUP BY C.Content_ID, C.Content_Title ORDER BY MAX(C.Content_DatePublished) ASC; 

Or with a correlated subquery:

SELECT C.Content_ID, C.Contt_Title,        (SELECT M.Media_Id         FROM tbl_Media M         WHERE M.Content_Id = C.Content_Id         ORDER BY M.MEDIA_ID DESC         LIMIT 1        ) as Media_Id FROM tbl_Contents C  ORDER BY C.Content_DatePublished ASC; 

Of course, the syntax for limit 1 varies between databases. Could be top. Or rownum = 1. Or fetch first 1 rows. Or something like that.