Left Join without duplicate rows from left table

by

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.

More Related Contents:

  1. Removing duplicate rows (based on values from multiple columns) from SQL table
  2. How to display data in JOIN, and return null value? [closed]
  3. How can I do a FULL OUTER JOIN in MySQL?
  4. INNER JOIN ON vs WHERE clause
  5. SQL JOIN – WHERE clause vs. ON clause
  6. SQL JOIN: is there a difference between USING, ON or WHERE?
  7. Difference between JOIN and INNER JOIN
  8. Why isn’t SQL ANSI-92 standard better adopted over ANSI-89?
  9. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  10. SQL to LINQ with multiple join, count and left join
  11. Oracle “(+)” Operator
  12. JOIN two SELECT statement results
  13. FULL OUTER JOIN with SQLite
  14. Implement paging (skip / take) functionality with this query
  15. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  16. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  17. Mixing implicit and explicit JOINs
  18. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  19. MySQL remove duplicates from big database quick
  20. Record returned from function has columns concatenated
  21. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  22. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  23. PostgreSQL JOIN with array type with array elements order, how to implement?
  24. How to exclude rows that don’t join with another table?
  25. INNER JOIN vs multiple table names in “FROM” [duplicate]
  26. Is NATURAL (JOIN) considered harmful in production environment?
  27. Re-Writing “Fuzzy Join” Functions from R to SQL
  28. SQL select join: is it possible to prefix all columns as ‘prefix.*’?
  29. sql query joins multiple tables – too slow (8 tables)
  30. Procedurally transform subquery into join

Leave a Comment