Sql Query not showing correct

Question

Post reply

Sql Query not showing correct

jsshivalik

SSChasing Mays

Points: 612

September 23, 2022 at 8:14 am

#4091094

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

Viewing 8 posts - 1 through 7 (of 7 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: 246979 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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246979 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 2 years, 2 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

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

MarkP Old Hand Points: 305 More actions · Answer 7

Add

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

to the bottom of your query.