Hi,
I have below table structure.
Having All Designations. CREATE TABLE [dbo].[Designation]( [DesignationNo] [int] IDENTITY(1,1) NOT NULL, [DesignationName] [nvarchar](50) NULL, CONSTRAINT [PK_Designation] PRIMARY KEY CLUSTERED ( [DesignationNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO Having All crew CREATE TABLE [dbo].[Crew]( [CrewNo] [int] IDENTITY(1,1) NOT NULL, [CrewName] [nvarchar](50) NULL, CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED ( [CrewNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO Having all location. CREATE TABLE [dbo].[Location]( [LocationNo] [int] IDENTITY(1,1) NOT NULL, [LocationName] [nvarchar](50) NULL, CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ( [LocationNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO here setting or fixing the structure what should be in particular crew based on crew and location show that if employee position is vacant or not.
CREATE TABLE [dbo].[CrewWiseDesignationRequired]( [CrewDesignNo] [int] IDENTITY(1,1) NOT NULL, [CrewDesig_DesignationNo] [int] NULL, [CrewDesig_CrewNo] [int] NULL, [CrewDesig_LocationNo] [int] NULL, CONSTRAINT [PK_CrewWiseDesignationRequired] PRIMARY KEY CLUSTERED ( [CrewDesignNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] WITH CHECK ADD CONSTRAINT [FK_CrewWiseDesignationRequired_Crew] FOREIGN KEY([CrewDesig_CrewNo]) REFERENCES [dbo].[Crew] ([CrewNo]) GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] CHECK CONSTRAINT [FK_CrewWiseDesignationRequired_Crew] GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] WITH CHECK ADD CONSTRAINT [FK_CrewWiseDesignationRequired_Designation] FOREIGN KEY([CrewDesignNo]) REFERENCES [dbo].[Designation] ([DesignationNo]) GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] CHECK CONSTRAINT [FK_CrewWiseDesignationRequired_Designation] GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] WITH CHECK ADD CONSTRAINT [FK_CrewWiseDesignationRequired_Location] FOREIGN KEY([CrewDesig_LocationNo]) REFERENCES [dbo].[Location] ([LocationNo]) GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] CHECK CONSTRAINT [FK_CrewWiseDesignationRequired_Location] GO below employee master.
CREATE TABLE [dbo].[Employee]( [EmployeeNo] [int] IDENTITY(1,1) NOT NULL, [EmployeeName] [nvarchar](50) NULL, [DesignationNo] [int] NULL, [CrewNo] [int] NULL, [CrewDesignNo] [int] NULL, [LocationNo] [int] NULL, CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED ( [EmployeeNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Crew] FOREIGN KEY([CrewNo]) REFERENCES [dbo].[Crew] ([CrewNo]) GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Crew] GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_CrewWiseDesignationRequired] FOREIGN KEY([CrewDesignNo]) REFERENCES [dbo].[CrewWiseDesignationRequired] ([CrewDesignNo]) GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_CrewWiseDesignationRequired] GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Designation] FOREIGN KEY([DesignationNo]) REFERENCES [dbo].[Designation] ([DesignationNo]) GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Designation] GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Location] FOREIGN KEY([LocationNo]) REFERENCES [dbo].[Location] ([LocationNo]) GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Location] GO how to write the query to show the vacant position in query.
example here set the crew designation wise.
Crew-A IT Manager IT Manager IT Assistant Programmer Crew-B IT Head IT Team Lead IT Team Lead
how to show below output.
Crew-A Alex IT Manager Vacant IT Manager Allen IT Assistant Rahul Programmer Crew-B Sachin IT Head Anil IT Team Lead Vacant IT Team Lead Ramesh Office Boy
below are insert query
INSERT INTO Crew (CrewNo, CrewName) VALUES ('1', 'Crew-A'); INSERT INTO Crew (CrewNo, CrewName) VALUES ('2', 'Crew-B'); INSERT INTO Designation (DesignationNo, DesignationName) VALUES ('1', 'IT Manager'); INSERT INTO Designation (DesignationNo, DesignationName) VALUES ('2', 'IT Assistant'); INSERT INTO Designation (DesignationNo, DesignationName) VALUES ('3', 'Programmer'); INSERT INTO Designation (DesignationNo, DesignationName) VALUES ('4', 'IT Head'); INSERT INTO Designation (DesignationNo, DesignationName) VALUES ('5', 'IT Team Lead'); INSERT INTO Designation (DesignationNo, DesignationName) VALUES ('6', 'Office Boy'); INSERT INTO Location (LocationNo, LocationName) VALUES ('1', 'Location-A'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('2', '1', '1', '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('3', '1', '1', '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('4', '1', '2', '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('5', '1', '3', '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('6', '2', '4', '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('14', '2', '5', '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) VALUES ('15', '2', '6', '1'); INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,CrewDesignNo,LocationNo) VALUES ('3', 'Alex', '1',2, '1'); INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,CrewDesignNo,LocationNo) VALUES ('4', 'Allen', '1',3, '1'); INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,CrewDesignNo,LocationNo) VALUES ('5', 'Rahul', '1', 4,'1'); INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,CrewDesignNo,LocationNo) VALUES ('6', 'Sachin', '1',5, '1'); INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,CrewDesignNo,LocationNo) VALUES ('7', 'Anil', '1', 6,'1'); INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,CrewDesignNo,LocationNo) VALUES ('8', 'Ramesh', '1',null, '1');
Thanks & regards,
Basit