Sql Query not showing correct

Question

Post reply

Sql Query not showing correct

Viewing 7 posts - 1 through 8 (of 8 total)

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

MarkP Old Hand Points: 305 More actions · Answer 1

Hello there. What query are you running to not return the right result?

jsshivalik SSChasing Mays Points: 612 More actions · Answer 2

Hi

SELECT dbo.TrainingHeader.TrainingID,dbo.TrainingHeader.TrainingNumber,dbo.TrainingHeader.TrainingTypeID,dbo.TrainingHeader.TrainingParticipantType,

dbo.TrainingHeader.TrainingMode,dbo.TrainingHeader.TrainingModuleID,dbo.TrainingHeader.FromDate,dbo.TrainingHeader.FromTime,

dbo.TrainingHeader.ToDate,dbo.TrainingHeader.ToTime,dbo.TrainingHeader.TrainerID,dbo.TrainingHeader.StateID,dbo.StateMaster.StateName,

dbo.TrainingHeader.LocationID,dbo.LocationMaster.LocationName,

dbo.TrainingHeader.TrainingStatus,(select dbo.EmployeeMaster.EmpName from dbo.EmployeeMaster where dbo.EmployeeMaster.EmpCID = dbo.TrainingHeader.TrainerID) as TrainerName,

dbo.TrainingHeader.Deleted, dbo.TrainingHeader.IsDrafted,dbo.TrainingHeader.PostedOn,

dbo.StateMaster.StateName AS StName, dbo.TrainingTypeMaster.Description,

dbo.TrainingNominees.NomineeId

from dbo.TrainingHeader Left Join

dbo.TrainingTypeMaster ON dbo.TrainingHeader.TrainingTypeID = dbo.TrainingTypeMaster.TrainingTypeID Left Join

dbo.TrainingModuleMaster ON dbo.TrainingHeader.TrainingModuleID = dbo.TrainingModuleMaster.TrainingModuleID Left Join

dbo.StateMaster ON dbo.TrainingHeader.StateID = dbo.StateMaster.StateCode Left Join

dbo.LocationMaster ON dbo.TrainingHeader.LocationID = dbo.LocationMaster.LocationID Left Join

dbo.SupportStaffMaster on dbo.SupportStaffMaster.SupportStaffID = dbo.TrainingHeader.SupportStaffID left Join

dbo.TrainingParticipants on dbo.TrainingParticipants.ParticipantID = dbo.TrainingHeader.TrainingID

left Join dbo.TrainingNominees on dbo.TrainingNominees.TrainingID = dbo.TrainingParticipants.TrainingID

and dbo.TrainingNominees.ParticipantID = dbo.TrainingParticipants.ParticipantID

Thanks

Phil Parkin SSC Guru Points: 247183 More actions · Answer 3

Is it so difficult to use code blocks?

SELECT dbo.TrainingHeader.TrainingID
      ,dbo.TrainingHeader.TrainingNumber
      ,dbo.TrainingHeader.TrainingTypeID
      ,dbo.TrainingHeader.TrainingParticipantType
      ,dbo.TrainingHeader.TrainingMode
      ,dbo.TrainingHeader.TrainingModuleID
      ,dbo.TrainingHeader.FromDate
      ,dbo.TrainingHeader.FromTime
      ,dbo.TrainingHeader.ToDate
      ,dbo.TrainingHeader.ToTime
      ,dbo.TrainingHeader.TrainerID
      ,dbo.TrainingHeader.StateID
      ,dbo.StateMaster.StateName
      ,dbo.TrainingHeader.LocationID
      ,dbo.LocationMaster.LocationName
      ,dbo.TrainingHeader.TrainingStatus
      ,TrainerName =
       (
           SELECT dbo.EmployeeMaster.EmpName
           FROM dbo.EmployeeMaster
           WHERE dbo.EmployeeMaster.EmpCID = dbo.TrainingHeader.TrainerID
       )
      ,dbo.TrainingHeader.Deleted
      ,dbo.TrainingHeader.IsDrafted
      ,dbo.TrainingHeader.PostedOn
      ,StName = dbo.StateMaster.StateName
      ,dbo.TrainingTypeMaster.Description
      ,dbo.TrainingNominees.NomineeId
FROM dbo.TrainingHeader
    LEFT JOIN dbo.TrainingTypeMaster
        ON dbo.TrainingHeader.TrainingTypeID = dbo.TrainingTypeMaster.TrainingTypeID
    LEFT JOIN dbo.TrainingModuleMaster
        ON dbo.TrainingHeader.TrainingModuleID = dbo.TrainingModuleMaster.TrainingModuleID
    LEFT JOIN dbo.StateMaster
        ON dbo.TrainingHeader.StateID = dbo.StateMaster.StateCode
    LEFT JOIN dbo.LocationMaster
        ON dbo.TrainingHeader.LocationID = dbo.LocationMaster.LocationID
    LEFT JOIN dbo.SupportStaffMaster
        ON dbo.SupportStaffMaster.SupportStaffID = dbo.TrainingHeader.SupportStaffID
    LEFT JOIN dbo.TrainingParticipants
        ON dbo.TrainingParticipants.ParticipantID = dbo.TrainingHeader.TrainingID
    LEFT JOIN dbo.TrainingNominees
        ON dbo.TrainingNominees.TrainingID = dbo.TrainingParticipants.TrainingID
           AND dbo.TrainingNominees.ParticipantID = dbo.TrainingParticipants.ParticipantID;

MarkP Old Hand Points: 305 More actions · Answer 4

Your join between TrainingHeader and TrainingParticipants (on ParticipantID = TrainingID) returns 13 rows as a left outer join and only 1 row as an inner join. Are you sure your join columns are correct? Joining them on TrainingID = TrainingID both makes more sense and returns 16 rows. Adding the TrainingNominees table to the query then inflates this to 20 rows (4 nulls in TrainingParticipants where TRGs 4, 7, 8 and 10 have no participants).

Phil Parkin SSC Guru Points: 247183 More actions · Answer 5

Using aliases for table names is also a good idea. Shortens the query and improves readability.

Any one of those left joins could be increasing the number of rows returned beyond what you are expecting. I suggest you change your query to SELECT * FROM .... and then start removing the joins one by one. At some point, you should find the one which is causing the trouble.

Another question, about TrainerName: why not just add a LEFT JOIN to EmployeeMaster?

SELECT th.TrainingID
      ,th.TrainingNumber
      ,th.TrainingTypeID
      ,th.TrainingParticipantType
      ,th.TrainingMode
      ,th.TrainingModuleID
      ,th.FromDate
      ,th.FromTime
      ,th.ToDate
      ,th.ToTime
      ,th.TrainerID
      ,th.StateID
      ,sm.StateName
      ,th.LocationID
      ,lm.LocationName
      ,th.TrainingStatus
      ,TrainerName =
       (
           SELECT em.EmpName
           FROM dbo.EmployeeMaster em
           WHERE em.EmpCID = th.TrainerID
       )
      ,th.Deleted
      ,th.IsDrafted
      ,th.PostedOn
      ,StName = sm.StateName
      ,ttm.Description
      ,tn.NomineeId
FROM dbo.TrainingHeader th
    LEFT JOIN dbo.TrainingTypeMaster ttm
        ON th.TrainingTypeID = ttm.TrainingTypeID
    LEFT JOIN dbo.TrainingModuleMaster tmm
        ON th.TrainingModuleID = tmm.TrainingModuleID
    LEFT JOIN dbo.StateMaster sm
        ON th.StateID = sm.StateCode
    LEFT JOIN dbo.LocationMaster lm
        ON th.LocationID = lm.LocationID
    LEFT JOIN dbo.SupportStaffMaster ssm
        ON ssm.SupportStaffID = th.SupportStaffID
    LEFT JOIN dbo.TrainingParticipants tp
        ON tp.ParticipantID = dbo.TrainingHeader.TrainingID
    LEFT JOIN dbo.TrainingNominees tn
        ON tn.TrainingID = tp.TrainingID
           AND tn.ParticipantID = tp.ParticipantID;

This reply was modified 3 years, 1 months ago by Phil Parkin.

jsshivalik SSChasing Mays Points: 612 More actions · Answer 6

Hi MarkP

If i use query it works as required , but i want to add table TrainingParticipant