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

score 2 · Accepted Answer · answered Sep 03 '16 at 15:29

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.

Marc Guillot · Answer 2 · 2016-09-03T16:36:52.690

You can get it very easily with just a subquery. Leave your query as it is, and add a subquery to get that information from Joints.

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,
                         (select top 1 dbo.Joints.TestPackageId from dbo.Joints where dbo.Joints.LineId = dbo.Materials.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

Thank you .Yes i know but i thought maybe there is a better solution . — Ehsan Akbar, Sep 03 '16 at 16:36

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

2 Answers2