Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INNER JOIN Distinct ID

Tags:

sql

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.

like image 543
user380432 Avatar asked Jul 16 '10 15:07

user380432


People also ask

Can we use distinct with join in SQL?

It is important to realize that if you have a properly designed and linked database, you can retrieve information from as many tables as you want, specify retrieval conditions based on any data in the tables, and show the results in any order that you like.

How do you avoid duplicates in inner join in SQL?

Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.

Is distinct better or GROUP BY?

DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY. In the following table duplicate records are present.

What does distinct * do?

The DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values. In other words, the DISTINCT keyword retrieves unique values from a table.


1 Answers

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
like image 61
Chuck Callebs Avatar answered Oct 21 '22 23:10

Chuck Callebs