Make one-to-one relation between two tables that have one-to-many relation in SQL

Question

I have these queries :

SELECT        
    dbo.Lines.Unit, dbo.Lines.LineNumber, dbo.Lines.DocumentNumber, 
    dbo.BaseMaterials.Name AS MaterialName, 
    dbo.MaterialDescriptions.Name AS MaterialDescription, 
    dbo.MaterialDescriptions.Description, 
    dbo.MaterialScopes.ScopeName, dbo.MaterialScopeObjectNames.ObjectName, 
    dbo.MaterialDescriptions.Size1, dbo.MaterialDescriptions.Size2, 
    dbo.MaterialDescriptions.ItemCode, dbo.Materials.Quantity, 
    dbo.Materials.Discipline, dbo.Materials.Id, dbo.Lines.Id AS LineId
FROM
    dbo.Materials 
INNER JOIN
    dbo.Lines ON dbo.Materials.LineId = dbo.Lines.Id 
INNER JOIN
    dbo.BaseMaterials ON dbo.Lines.BaseMaterialId = dbo.BaseMaterials.Id 
INNER JOIN
    dbo.MaterialDescriptions ON dbo.Materials.MaterialDescriptionId = dbo.MaterialDescriptions.Id 
INNER JOIN
    dbo.MaterialScopes ON dbo.MaterialDescriptions.MaterialScopeId = dbo.MaterialScopes.Id 
INNER JOIN
    dbo.MaterialScopeObjectNames ON dbo.MaterialDescriptions.MaterialScopeObjectId = dbo.MaterialScopeObjectNames.Id

It returns 16000 records. I have another tables with joints name that has a relation with material table on lineId every material can have multi joints so my query when I add joints table is like this :

SELECT        
    dbo.Lines.Unit, dbo.Lines.LineNumber, dbo.Lines.DocumentNumber, 
    dbo.BaseMaterials.Name AS MaterialName, 
    dbo.MaterialDescriptions.Name AS MaterialDescription, 
    dbo.MaterialDescriptions.Description, dbo.MaterialScopes.ScopeName, 
    dbo.MaterialScopeObjectNames.ObjectName, dbo.MaterialDescriptions.Size1, 
    dbo.MaterialDescriptions.Size2, dbo.MaterialDescriptions.ItemCode, 
    dbo.Materials.Quantity, dbo.Materials.Discipline, dbo.Materials.Id, 
    dbo.Lines.Id AS LineId, dbo.Joints.TestPackageId
FROM
    dbo.Materials 
INNER JOIN
    dbo.Lines ON dbo.Materials.LineId = dbo.Lines.Id 
INNER JOIN
    dbo.BaseMaterials ON dbo.Lines.BaseMaterialId = dbo.BaseMaterials.Id 
INNER JOIN
    dbo.MaterialDescriptions ON dbo.Materials.MaterialDescriptionId = dbo.MaterialDescriptions.Id 
INNER JOIN
    dbo.MaterialScopes ON dbo.MaterialDescriptions.MaterialScopeId = dbo.MaterialScopes.Id 
INNER JOIN
    dbo.MaterialScopeObjectNames ON dbo.MaterialDescriptions.MaterialScopeObjectId = dbo.MaterialScopeObjectNames.Id 
INNER JOIN
    dbo.Joints ON dbo.Materials.LineId = dbo.Joints.LineId

As you can see in the last line i make a join between joints and materials to access the testpackageid column in joints table .but my result returns 220000records .How can i change the join type i mean left or right to just returns 16000 records with its testpackageId

Answer 1

You can't using JOIN. However, you can using CROSS APPLY:

FROM . . .
     dbo.MaterialScopeObjectNames
     ON dbo.MaterialDescriptions.MaterialScopeObjectId = dbo.MaterialScopeObjectNames.Id CROSS APPLY
     (SELECT TOP 1 j.*
      FROM dbo.Joints j
      WHERE dbo.Materials.LineId = j.LineId
     ) J

Normally, you would include an ORDER BY when using TOP so you have some control over the row being returned.

Also note that the use of table aliases makes the query easier to write and to read.