What can cause a row to repeat when joining multiple tables?

Question

SQL Server

use PhonesShop
declare @SettingForFilter date
set @SettingForFilter = '2021-10-12'
select distinct *
from Orders LEFT JOIN Customers on Orders.CustomerCode = Customers.CustomerCode 
LEFT JOIN Accessories on Orders.AccessorieCode1 = Accessories.AccessorieCode or Orders.AccessorieCode2 = Accessories.AccessorieCode or Orders.AccessorieCode3 = Accessories.AccessorieCode 
LEFT JOIN Services on Orders.ServiceCode1 = Services.ServiceCode or Orders.ServiceCode2 = Services.ServiceCode or Orders.ServiceCode3 = Services.ServiceCode
LEFT JOIN Workers on Orders.WorkerCode = Workers.WorkerCode
where Orders.OrderDate = @SettingForFilter

I can't understand why I get a duplicate (as many as 18 identical lines)

Answer 1

LEFT JOIN Accessories on Orders.AccessorieCode1 = Accessories.AccessorieCode 
  OR Orders.AccessorieCode2 = Accessories.AccessorieCode 
  OR Orders.AccessorieCode3 = Accessories.AccessorieCode

This connection is repeated 3 times (for each field).

LEFT JOIN Services on Orders.ServiceCode1 = Services.ServiceCode 
 OR Orders.ServiceCode2 = Services.ServiceCode 
 OR Orders.ServiceCode3 = Services.ServiceCode

And 3 more times. Already 3 * 3 = 9 times, for example, the first lines.
Therefore, you need to bind each field separately:

SELECT *
  FROM Orders 
    LEFT OUTER JOIN Accessories AS A1 ON Orders.AccessorieCode1 = A1.AccessorieCode 
    LEFT OUTER JOIN Accessories AS A2 ON Orders.AccessorieCode2 = A2.AccessorieCode
    LEFT OUTER JOIN Accessories AS A3 ON Orders.AccessorieCode3 = A3.AccessorieCode

Answer 2

you need to firmly remember that
JOIN
is a Cartesian product
i.e. with your 4 JOIN combinations there will be 2 in the cube
, and already all sorts of WHEREs thin it out and discard unsatisfactory elements