Sql Query not showing correct

  • Hi

    I have 3 tables . It should return 16 rows . Since there are 2 record of Training Id 6,11,12 on tblnominees

    USE [TMM]
    GO
    /****** Object: Table [dbo].[TrainingHeader] Script Date: 23/09/2022 13:40:22 ******/SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TrainingHeader](
    [TrainingID] [int] IDENTITY(1,1) NOT NULL,
    [TrainingNumber] [nvarchar](50) NULL,
    [TrainingTypeID] [int] NULL,
    [TrainingModuleID] [int] NULL,
    [FromDate] [date] NULL,
    [FromTime] [time](7) NULL,
    [ToDate] [date] NULL,
    [ToTime] [time](7) NULL,
    [TrainingParticipantType] [nvarchar](100) NULL,
    [TrainingMode] [nvarchar](100) NULL,
    [StateID] [int] NULL,
    [LocationID] [int] NULL,
    [AcademyID] [int] NULL,
    [TrainerID] [int] NULL,
    [SupportStaffID] [int] NULL,
    [UserRemarks] [nvarchar](max) NULL,
    [TrainingStatus] [nvarchar](50) NULL,
    [TrainingCompleted] [bit] NULL,
    [TrainingCompletedOn] [datetime] NULL,
    [PostedBy] [nvarchar](20) NULL,
    [Department] [nvarchar](100) NULL,
    [CancellationDate] [datetime] NULL,
    [CancelReason] [nvarchar](max) NULL,
    [IsDrafted] [bit] NULL,
    [Deleted] [bit] NULL,
    [EntryDate] [datetime] NULL,
    [PostedOn] [datetime] NULL,
    [ModifiedOn] [datetime] NULL,
    CONSTRAINT [PK_TrainingHeader] PRIMARY KEY CLUSTERED
    (
    [TrainingID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    /****** Object: Table [dbo].[TrainingNominees] Script Date: 23/09/2022 13:40:22 ******/SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TrainingNominees](
    [TrainingNomineeID] [int] IDENTITY(1,1) NOT NULL,
    [TrainingID] [int] NULL,
    [TrainingParticipantType] [nvarchar](50) NULL,
    [ParticipantID] [int] NULL,
    [NomineeId] [int] NULL,
    [Deleted] [bit] NULL,
    [EntryDate] [datetime] NULL,
    CONSTRAINT [PK_TrainingNominees] PRIMARY KEY CLUSTERED
    (
    [TrainingNomineeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    /****** Object: Table [dbo].[TrainingParticipants] Script Date: 23/09/2022 13:40:22 ******/SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TrainingParticipants](
    [TrainingParticipantID] [int] IDENTITY(1,1) NOT NULL,
    [TrainingID] [int] NULL,
    [TrainingParticipantType] [nvarchar](50) NULL,
    [ParticipantID] [int] NULL,
    [Deleted] [bit] NULL,
    [EntryDate] [datetime] NULL,
    CONSTRAINT [PK_TrainingParticipants] PRIMARY KEY CLUSTERED
    (
    [TrainingParticipantID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[TrainingHeader] ON

    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (1, N'TRG-00001', 10002, 11002, CAST(N'2022-09-11' AS Date), CAST(N'09:00:00' AS Time), CAST(N'2022-09-11' AS Date), CAST(N'11:30:00' AS Time), N'2', N'Offline', 10, 172, NULL, 162, 16001, N'Remarks', N'Completed', 1, CAST(N'2022-09-14T09:41:58.827' AS DateTime), N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-05T05:52:20.787' AS DateTime), NULL, CAST(N'2022-09-05T05:59:28.060' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (2, N'TRG-00002', 10001, 11001, CAST(N'2022-09-06' AS Date), CAST(N'13:00:00' AS Time), CAST(N'2022-09-06' AS Date), CAST(N'16:00:00' AS Time), N'4', N'Online', 17, 288, NULL, 5805, 0, N'test training. you may attend training online using Teams link....any issues during join meeting you may call us 7676358585', N'Completed', 1, CAST(N'2022-09-09T18:21:11.743' AS DateTime), N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-06T13:33:46.933' AS DateTime), NULL, CAST(N'2022-09-06T13:36:56.263' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (3, N'TRG-00003', 10002, 11030, CAST(N'2022-09-22' AS Date), CAST(N'08:45:00' AS Time), CAST(N'2022-09-30' AS Date), CAST(N'10:15:00' AS Time), N'1', N'Online', 7, 131, NULL, 5785, 16003, N'', N'In-Progress', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-06T13:37:02.637' AS DateTime), NULL, CAST(N'2022-09-22T08:53:47.473' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (4, N'TRG-00004', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', NULL, NULL, NULL, NULL, NULL, N'', N'Drafted', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-06T13:39:38.030' AS DateTime), NULL, CAST(N'2022-09-06T13:39:38.030' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (5, N'TRG-00005', 10001, 11005, CAST(N'2022-09-07' AS Date), CAST(N'09:00:00' AS Time), CAST(N'2022-09-07' AS Date), CAST(N'18:00:00' AS Time), N'3', N'Offline', 17, 312, NULL, 5222, 0, N'hi ...pelase come to the training on time...hi ...pelase come to the training on time...hi ...pelase come to the training on time...hi ...pelase come to the training on time...hi ...pelase come to the training on time...hi ...pelase come to the training on time...hi ...pelase come to the training on', N'Completed', 1, CAST(N'2022-09-09T18:21:11.743' AS DateTime), N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-06T17:17:52.863' AS DateTime), NULL, CAST(N'2022-09-06T17:28:15.933' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (6, N'TRG-00006', 10002, 11030, CAST(N'2022-09-17' AS Date), CAST(N'08:00:00' AS Time), CAST(N'2022-09-20' AS Date), CAST(N'08:45:00' AS Time), N'1', N'Online', 6, 127, NULL, 2946, 16001, N'Training', N'Completed', 1, CAST(N'2022-09-20T10:47:03.573' AS DateTime), N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-17T21:46:43.513' AS DateTime), NULL, CAST(N'2022-09-17T22:03:24.563' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (7, N'TRG-00007', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', NULL, NULL, NULL, NULL, NULL, N'', N'Drafted', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-20T10:50:26.040' AS DateTime), NULL, CAST(N'2022-09-20T10:50:26.040' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (8, N'TRG-00008', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', NULL, NULL, NULL, NULL, NULL, N'', N'Drafted', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-20T10:53:51.717' AS DateTime), NULL, CAST(N'2022-09-20T10:53:51.717' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (9, N'TRG-00009', 10002, 11034, CAST(N'2022-09-20' AS Date), CAST(N'08:00:00' AS Time), CAST(N'2022-09-24' AS Date), CAST(N'10:00:00' AS Time), N'1', N'Online', 6, 127, NULL, 2946, 16003, N'Remarks', N'In-Progress', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-20T11:00:08.713' AS DateTime), NULL, CAST(N'2022-09-20T11:02:12.620' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (10, N'TRG-00010', NULL, NULL, NULL, NULL, NULL, NULL, N'', N'', NULL, NULL, NULL, NULL, NULL, N'', N'Drafted', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-20T11:08:48.367' AS DateTime), NULL, CAST(N'2022-09-20T11:08:48.367' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (11, N'TRG-00011', 10002, 11034, CAST(N'2022-09-20' AS Date), CAST(N'08:00:00' AS Time), CAST(N'2022-09-22' AS Date), CAST(N'11:00:00' AS Time), N'1', N'Online', 6, 127, NULL, 2946, 16003, N'Remarks', N'Completed', 1, CAST(N'2022-09-22T13:37:43.660' AS DateTime), N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-20T11:09:51.033' AS DateTime), NULL, CAST(N'2022-09-20T11:10:36.133' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (12, N'TRG-00012', 10002, 11030, CAST(N'2022-09-22' AS Date), CAST(N'08:00:00' AS Time), CAST(N'2022-09-30' AS Date), CAST(N'10:15:00' AS Time), N'1', N'Online', 7, 132, NULL, 2946, 16002, N'Remarks', N'In-Progress', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-22T14:14:20.503' AS DateTime), NULL, CAST(N'2022-09-22T21:08:23.613' AS DateTime))
    INSERT [dbo].[TrainingHeader] ([TrainingID], [TrainingNumber], [TrainingTypeID], [TrainingModuleID], [FromDate], [FromTime], [ToDate], [ToTime], [TrainingParticipantType], [TrainingMode], [StateID], [LocationID], [AcademyID], [TrainerID], [SupportStaffID], [UserRemarks], [TrainingStatus], [TrainingCompleted], [TrainingCompletedOn], [PostedBy], [Department], [CancellationDate], [CancelReason], [IsDrafted], [Deleted], [EntryDate], [PostedOn], [ModifiedOn]) VALUES (13, N'TRG-00013', 10002, 11030, CAST(N'2022-09-23' AS Date), CAST(N'08:00:00' AS Time), CAST(N'2022-09-30' AS Date), CAST(N'10:00:00' AS Time), N'1', N'Online', 6, 127, NULL, 2946, 16003, N'Remarks', N'In-Progress', 0, NULL, N'990', N'Information Technology', NULL, NULL, 1, 0, CAST(N'2022-09-22T22:20:49.837' AS DateTime), NULL, CAST(N'2022-09-22T22:21:35.203' AS DateTime))
    SET IDENTITY_INSERT [dbo].[TrainingHeader] OFF
    GO
    SET IDENTITY_INSERT [dbo].[TrainingNominees] ON

    INSERT [dbo].[TrainingNominees] ([TrainingNomineeID], [TrainingID], [TrainingParticipantType], [ParticipantID], [NomineeId], [Deleted], [EntryDate]) VALUES (1, 6, N'1', 12001, 14001, 0, CAST(N'2022-09-17T22:04:11.330' AS DateTime))
    INSERT [dbo].[TrainingNominees] ([TrainingNomineeID], [TrainingID], [TrainingParticipantType], [ParticipantID], [NomineeId], [Deleted], [EntryDate]) VALUES (2, 6, N'1', 12002, 14002, 0, CAST(N'2022-09-17T22:04:21.467' AS DateTime))
    INSERT [dbo].[TrainingNominees] ([TrainingNomineeID], [TrainingID], [TrainingParticipantType], [ParticipantID], [NomineeId], [Deleted], [EntryDate]) VALUES (3, 11, N'1', 12001, 14001, 0, CAST(N'2022-09-20T11:11:01.900' AS DateTime))
    INSERT [dbo].[TrainingNominees] ([TrainingNomineeID], [TrainingID], [TrainingParticipantType], [ParticipantID], [NomineeId], [Deleted], [EntryDate]) VALUES (4, 11, N'1', 12002, 14002, 0, CAST(N'2022-09-20T11:11:08.070' AS DateTime))
    INSERT [dbo].[TrainingNominees] ([TrainingNomineeID], [TrainingID], [TrainingParticipantType], [ParticipantID], [NomineeId], [Deleted], [EntryDate]) VALUES (18, 12, N'1', 12002, 14002, 0, CAST(N'2022-09-22T21:05:16.467' AS DateTime))
    INSERT [dbo].[TrainingNominees] ([TrainingNomineeID], [TrainingID], [TrainingParticipantType], [ParticipantID], [NomineeId], [Deleted], [EntryDate]) VALUES (19, 12, N'1', 12001, 14001, 0, CAST(N'2022-09-22T21:08:40.583' AS DateTime))
    SET IDENTITY_INSERT [dbo].[TrainingNominees] OFF
    GO
    SET IDENTITY_INSERT [dbo].[TrainingParticipants] ON

    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (2, 1, N'2', 1, 0, NULL)
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (3, 2, N'4', 5779, 0, NULL)
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (12, 5, N'3', 13001, 0, NULL)
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (13, 5, N'3', 13002, 0, NULL)
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (16, 6, N'1', 12001, 0, CAST(N'2022-09-17T22:03:24.630' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (17, 6, N'1', 12002, 0, CAST(N'2022-09-17T22:03:24.630' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (20, 9, N'1', 12007, 0, CAST(N'2022-09-20T11:02:12.747' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (21, 9, N'1', 12008, 0, CAST(N'2022-09-20T11:02:12.747' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (22, 11, N'1', 12002, 0, CAST(N'2022-09-20T11:10:36.177' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (23, 11, N'1', 12001, 0, CAST(N'2022-09-20T11:10:36.180' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (24, 3, N'1', 12002, 0, CAST(N'2022-09-22T08:53:48.067' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (25, 3, N'1', 12001, 0, CAST(N'2022-09-22T08:53:48.077' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (1026, 12, N'1', 12002, 0, CAST(N'2022-09-22T21:08:23.873' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (1027, 12, N'1', 12001, 0, CAST(N'2022-09-22T21:08:23.880' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (1028, 13, N'1', 12002, 0, CAST(N'2022-09-22T22:21:35.537' AS DateTime))
    INSERT [dbo].[TrainingParticipants] ([TrainingParticipantID], [TrainingID], [TrainingParticipantType], [ParticipantID], [Deleted], [EntryDate]) VALUES (1029, 13, N'1', 12001, 0, CAST(N'2022-09-22T22:21:35.543' AS DateTime))
    SET IDENTITY_INSERT [dbo].[TrainingParticipants] OFF
    GO
    ALTER TABLE [dbo].[TrainingHeader] ADD CONSTRAINT [DF_TrainingHeader_IsDrafted] DEFAULT ((0)) FOR [IsDrafted]
    GO
    ALTER TABLE [dbo].[TrainingNominees] ADD CONSTRAINT [DF_TrainingNominees_Deleted] DEFAULT ((0)) FOR [Deleted]
    GO
    ALTER TABLE [dbo].[TrainingNominees] ADD CONSTRAINT [DF_TrainingNominees_EntryDate] DEFAULT (getdate()) FOR [EntryDate]
    GO
    ALTER TABLE [dbo].[TrainingParticipants] ADD CONSTRAINT [DF_TrainingParticipants_Deleted] DEFAULT ((0)) FOR [Deleted]
    GO
    ALTER TABLE [dbo].[TrainingParticipants] ADD CONSTRAINT [DF_TrainingParticipants_EntryDate] DEFAULT (getdate()) FOR [EntryDate]
    GO
    ALTER TABLE [dbo].[TrainingHeader] WITH CHECK ADD CONSTRAINT [FK_TrainingHeader_LocationMaster] FOREIGN KEY([LocationID])
    REFERENCES [dbo].[LocationMaster] ([LocationID])
    GO
    ALTER TABLE [dbo].[TrainingHeader] CHECK CONSTRAINT [FK_TrainingHeader_LocationMaster]
    GO
    ALTER TABLE [dbo].[TrainingHeader] WITH CHECK ADD CONSTRAINT [FK_TrainingHeader_StateMaster] FOREIGN KEY([StateID])
    REFERENCES [dbo].[StateMaster] ([StateCode])
    GO
    ALTER TABLE [dbo].[TrainingHeader] CHECK CONSTRAINT [FK_TrainingHeader_StateMaster]
    GO
    ALTER TABLE [dbo].[TrainingHeader] WITH CHECK ADD CONSTRAINT [FK_TrainingHeader_TrainingModuleMaster] FOREIGN KEY([TrainingModuleID])
    REFERENCES [dbo].[TrainingModuleMaster] ([TrainingModuleID])
    GO
    ALTER TABLE [dbo].[TrainingHeader] CHECK CONSTRAINT [FK_TrainingHeader_TrainingModuleMaster]
    GO
    ALTER TABLE [dbo].[TrainingHeader] WITH CHECK ADD CONSTRAINT [FK_TrainingHeader_TrainingTypeMaster] FOREIGN KEY([TrainingTypeID])
    REFERENCES [dbo].[TrainingTypeMaster] ([TrainingTypeID])
    GO
    ALTER TABLE [dbo].[TrainingHeader] CHECK CONSTRAINT [FK_TrainingHeader_TrainingTypeMaster]
    GO
    ALTER TABLE [dbo].[TrainingParticipants] WITH CHECK ADD CONSTRAINT [FK_TrainingParticipants_TrainingHeader] FOREIGN KEY([TrainingID])
    REFERENCES [dbo].[TrainingHeader] ([TrainingID])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    GO
    ALTER TABLE [dbo].[TrainingParticipants] CHECK CONSTRAINT [FK_TrainingParticipants_TrainingHeader]
    GO

    Thanks

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

  • 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

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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).

  • 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 1 day, 9 hours ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi MarkP

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

    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.TrainingNominees on dbo.TrainingNominees.TrainingID = dbo.TrainingHeader.TrainingID

     

    Thanks

  • Add

    LEFT OUTER JOIN dbo.TrainingParticipants ON dbo.TrainingParticipants.TrainingID = dbo.TrainingHeader.TrainingID

    to the bottom of your query.

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

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