Left Join Without Duplicate Rows from Left Table

Left Join without duplicate rows from left table

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.

Mysql Left join without duplicate value from left table with the last value from it

You can use something like this:

 select  marketing.*,
         phonecall.db_id,
         phonecall.db_due,
         phonecall.db_nextdate,
         phonecall.db_pnote
 from tbl_marketing marketing
 left outer join tbl_phonecall phonecall on phonecall.db_mid=marketing.db_maid
     and phonecall.db_due=(select max(t2.db_due) from tbl_phonecall t2
                            where t2.db_mid=marketing.db_maid)
     and phonecall.db_nextdate=(select max(t3.db_nextdate) from tbl_phonecall t3
                            where t3.db_mid=marketing.db_maid)

Left Outer Join Without Duplicate Rows

I think the problem is that the conditions are in the WHERE clause - try moving the conditions from the WHERE clause and into each join:

SELECT dbo.product.id, dbo.product.name, dbo.product_price.value, 
dbo.product_barcode.barcode
FROM dbo.product
LEFT OUTER JOIN dbo.product_price ON dbo.product.id = dbo.product_price.product_id
  --moved from WHERE clause
  AND dbo.product_price.id IN (SELECT MIN(id) AS minPriceID FROM dbo.product_price AS product_price_1 GROUP BY product_id)
LEFT OUTER JOIN dbo.product_barcode ON dbo.product.id = dbo.product_barcode.product_id
  --moved from WHERE clause
  AND dbo.product_barcode.id IN (SELECT MIN(id) AS Expr1 FROM dbo.product_barcode AS product_barcode_1 GROUP BY product_id)

Prevent duplicate values in LEFT JOIN

I like to call this problem "cross join by proxy". Since there is no information (WHERE or JOIN condition) how the tables department and contact are supposed to match up, they are cross-joined via the proxy table person - giving you the Cartesian product. Very similar to this one:

• Two SQL LEFT JOINS produce incorrect result

More explanation there.

Solution for your query:

SELECT p.id, p.person_name, d.department_name, c.phone_number
FROM   person p
LEFT   JOIN (
   SELECT person_id, min(department_name) AS department_name
   FROM   department
   GROUP  BY person_id
   ) d ON d.person_id = p.id
LEFT   JOIN (
   SELECT person_id, min(phone_number) AS phone_number
   FROM   contact
   GROUP  BY person_id
   ) c ON c.person_id = p.id;

You did not define which department or phone number to pick, so I arbitrarily chose the minimum. You can have it any other way ...

Left Join without duplicate values from the left table when multiple values in right table

Maybe this query would work for you. You do not need to order by Clocktime as the minimum value will be brought through from the MIN aggregate.

I would also change the Clocktime field to TIME if you are using SQL 2008 so that you can avoid the substring convert.

set language british 
SELECT dbo.Employee.EmployeeID, SUBSTRING(CONVERT(varchar, MIN(ClockTemp.ClockTime), 108), 1, 5) as ClockTime
FROM  dbo.Employee LEFT JOIN 
(
    Select [ClockID]
      ,[EmployeeID]
      ,[ClockTypeID]
      ,[ClockDate]
      ,[ClockTime] 
    FROM dbo.Clock 
    WHERE dbo.Clock.ClockDate = DATEADD(DD, 0, CAST('07-11-2016' AS DATE)) AND Clock.ClockTypeID=1
) As ClockTemp
ON dbo.Employee.EmployeeID = ClockTemp.EmployeeID
GROUP BY dbo.Employee.EmployeeID
ORDER BY dbo.Employee.EmployeeID

---

Related Topics