Post reply

How to know relations between tables after first relations ?

  • December 20, 2019 at 1:36 am

    #3709284

    I have SQL query based on relations between multi tables as followings :

    select 
    t.PartNumber, t.CompanyName, t.ReliabilityStatus, t.ESDStatus, t.CECCStatus, t.AgencyApprovalStatus, t.FlammabilityStatus, t.ESCCStatus, t.MedicalStatus,t.Automotive,QR.QualificationRange,QR.QualificationRangeMapping,A.Name as ZTemperatureGrade,t.QualificationApplication,t.COTS,t.Military ,[Status]
    from Parts t left join Parts.QualificationSettings QS on t.PartID=QS.PartID inner join [dbo].[Nop_AcceptedValuesOption] A on A.AcceptedValuesOptionID = QS.ZTemperatureGradeID left join parts.QualificationRanges QR on QR.QualificationRangeID=QS.QualificationRangeID

    I have multi relations here as follwoing :

    1- parts t left join Parts.QualificationSettings QS on t.PartID=QS.PartID

    2- inner join [dbo].[Nop_AcceptedValuesOption] A on A.AcceptedValuesOptionID = QS.ZTemperatureGradeID

    3- left join parts.QualificationRanges QR on QR.QualificationRangeID=QS.QualificationRangeID

    first relations i do it left join between two tables parts and QualificationSettings because i need all records on table parts a and related and not related from table QualificationSetting

    second relation inner join between two tables [dbo].[Nop_AcceptedValuesOption] AND QualificationSettings what meaning

    and correct is left or inner

    third relations left join between two tables QualificationRanges and QualificationSettings what meaning and correct is left or inner

    can you please explain to me second and third relation what mean

  • December 20, 2019 at 2:41 am

    #3709292

    First, if you add a few new-lines the query becomes readable:

    SELECT t.PartNumber, 
           t.CompanyName, 
           t.ReliabilityStatus, 
           t.ESDStatus, 
           t.CECCStatus, 
           t.AgencyApprovalStatus, 
           t.FlammabilityStatus, 
           t.ESCCStatus, 
           t.MedicalStatus, 
           t.Automotive, 
           QR.QualificationRange, 
           QR.QualificationRangeMapping, 
           A.Name AS ZTemperatureGrade, 
           t.QualificationApplication, 
           t.COTS, 
           t.Military, 
           [Status]
      FROM Parts t
      LEFT JOIN Parts.QualificationSettings QS 
             ON t.PartID = QS.PartID
     INNER JOIN [dbo].[Nop_AcceptedValuesOption] A
             ON A.AcceptedValuesOptionID = QS.ZTemperatureGradeID
      LEFT JOIN parts.QualificationRanges QR 
             ON QR.QualificationRangeID = QS.QualificationRangeID;

     

    I don't really understand what your question is, but the query smells.

    You have a left join to QS followed by an inner join on QS to A. This means that the left join on QS is really an inner join. I think you just want all the joins to be LEFT JOIN.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply