Left Join Using Distinct

SELECT DISTINCT with LEFT JOIN, ORDERed BY in t-SQL

When you narrow it down to individual id's, you create the possibility that each id might have more than one dtOut associated with it. When that happens, how will Sql Server know which order to use?

You could try:

SELECT t1.id
FROM tbl t1
LEFT JOIN  tbl t2 on t1.type = t2.type AND t1.dtIn = t2.dtIn
GROUP BY t1.id, t2.dtOut
ORDER BY t2.dtOut

However, as I mentioned above this can open the possibility of having the same id listed more than once, if it matches to more than one record on the right-side table.

MySQL Select Distinct with Left Join?

The easiest way to think about this is to first find the all the companies that have company level notes, which you can do with

 select distinct c.company_id
   from company c 
 inner join notes n 
     on c.company_id = n.company_id 
  where n.location_id is null;

Then simply remove these companies from the company select:

select company_id,
       name
  from company
 where company_id not in (select distinct c.company_id
                            from company c 
                          inner join notes n 
                              on c.company_id = n.company_id 
                           where n.location_id is null);

*Updated to use inner join instead of comma-separated join.

SQL - remove duplicates from left join

This will give the results you requested and should have the best performance.

SELECT     
    OBJECTID
    , PKID
    , t2.SUBDIVISIO,
    , t2.ASBUILT1

FROM        dbo.table1 AS t1
OUTER APPLY (
    SELECT  TOP 1 *
    FROM    dbo.table2 AS t2
    WHERE   t1.PKID = t2.FKID
    ) AS t2

How to get unique values in multiple left join

Wrap your query inside a CTE with a new column that you will use to filter the results.
This new column is produced with ROW_NUMBER() window function partitioned by Dest.Code:

WITH cte as (
  SELECT Dest.Code, City.CityName, Country.Id,
  ROW_NUMBER() OVER (PARTITION BY Dest.Code ORDER BY City.CityName, Country.Id) AS rn   
  FROM Dest
  LEFT JOIN City ON Dest.CityId = City.Id
  LEFT JOIN Country ON City.CountryId = Country.Id
)
SELECT Code, CityName, Id
FROM cte
WHERE rn = 1

select distinct id when using left join and order by on two tables

You can use aggregate functions in ORDER BY:

SELECT shops.id AS shopid -- No need to use MAX(...) here
FROM shops 
LEFT JOIN expiration ON shops.id=expiration.shopid
GROUP BY shopid
ORDER BY
  MAX(shops.grade) DESC,
  MIN(expiration.startdate) ASC,
  MAX(expiration.enddate) DESC,
  shops.id

Postgres - Left join using a where clause + distinct

You have a collision of ids. Be explicit about the columns you are selecting.

Then I think you have basically the right logic:

SELECT p.post_id, p.date_posted, p.posted_by,
       v.user_id, v.votes
FROM posts p LEFT JOIN
     voted v
     ON p.post_id = v.id
ORDER BY p.date_posted DESC
FETCH FIRST 5 ROW ONLY ;

DISTINCT is not working properly in left join

A simple GROUP BY with normal SUM's should do.

SELECT
 si.Id, 
 si.AssetsName, 
 si.Rate,
 si.Qty,
 SUM(so.QtyOut) AS QtyOut,  
 COALESCE(si.Qty, 0) - SUM(so.QtyOut) AS Balance
FROM dbo.StockIn si 
LEFT JOIN dbo.StockOut so ON so.Id = si.Id
GROUP BY si.Id, si.AssetsName, si.Rate, si.Qty;