INNER JOIN Distinct ID

Question

I have the following code:

FROM CTE_Order cte
    INNER JOIN tblOrders o
       ON cte.OrderId = o.Id
    INNER JOIN tblOrderUnits ou
       ON o.id = ou.OrderId                        
    INNER JOIN tblOrderServiceUnits osu
       ON ou.VMSUnitID = osu.UnitId

When I join the ou I get 2 of the same unit Id's. This make the Inner Join tblOrderServiceUnits return 4 rows with 2 being duplicates. I need it to only return the 2 rows the are different. How do I use a distinct to Inner Join only distinct ou.id?

Sorry for the bad explanation but basically I am jsut trying to see how an INNER JOIN with a distinct subquery would work, If someone could give me an example of that I could figure it out from there.

Chuck Callebs · Accepted Answer · 2010-07-16T16:10:33.997

20

INNER JOIN (SELECT DISTINCT * FROM X) Alias
ON Alias.ID = Primary.ID

For your example:

INNER JOIN (SELECT DISTINCT VMSUnitID, OrderId FROM tblOrderUnits) ou
ON o.id = ou.OrderId

edited Jul 16 '10 at 16:10

answered Jul 16 '10 at 15:14

Chuck Callebs

16,293
8
56
71

So If i need to select distinct VMSUnitID in the OrderUnit join how would I do that? – user380432 Jul 16 '10 at 15:30
Updated post for clarification. – Chuck Callebs Jul 16 '10 at 16:11
If a table contained only id column and used as filter, what is more optimal to do INNER JOIN or instead of joining do `WHERE someId IN (SELECT DISTINCT * FROM X)` – Pawel Cioch Apr 21 '22 at 18:59

INNER JOIN Distinct ID

1 Answers1

Linked