Click here to Skip to main content
15,937,186 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have the below table structure.

Having All Designations.
SQL
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 locations:
SQL
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.
SQL
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]

GO

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.

The example here sets 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 the below output:
Employee Master	
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 is the insert query:
SQL
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');


What I have tried:

I tried the below query, but even the result was not exactly coming.
SQL
SELECT
    'Employee Master' AS [Employee Master],
    c.[CrewName] AS [Crew],
    COALESCE(e.[EmployeeName], 'Vacant') AS [EmployeeName],
    COALESCE(d.[DesignationName], 'Vacant') AS [Designation]
FROM
    [dbo].[Crew] c
CROSS JOIN
    [dbo].[Designation] d
CROSS JOIN
    [dbo].[Location] l
LEFT JOIN
    [dbo].[CrewWiseDesignationRequired] cr _
    ON c.[CrewNo] = cr.[CrewDesig_CrewNo]
                     AND d.[DesignationNo] = cr.[CrewDesig_DesignationNo]
                     AND l.[LocationNo] = cr.[CrewDesig_LocationNo]
LEFT JOIN
    [dbo].[Employee] e ON cr.[CrewDesignNo] = e.[CrewDesignNo]
                     AND cr.[CrewDesig_LocationNo] = e.[LocationNo]
                     AND c.[CrewNo] = e.[CrewNo]
ORDER BY
    c.[CrewName], d.[DesignationName], e.[EmployeeName];
Posted
Updated 13-Sep-23 6:35am
v3
Comments
[no name] 30-Aug-23 16:21pm    
Over-engineered. A simple "crew member" table should have been sufficient. Or a "pre" extract / join of a bunch of lookups before doing the main query (considering the low volume probably involved).
CHill60 1-Sep-23 9:34am    
I have just tried to recreate your problem using the code you have supplied and I am not going to persist any further. Why? Because I get errors trying to run your code to produce your tables - things like defining an IDENTITY column but then providing the value (without letting me know I need to turn IDENTITY INSERT on). Then when I fix that, I get a conflict with a foreign key constraint.
I commend you for providing the code to recreate your sample data - but please make sure it works. I do this in my spare time at work and my lunch hour just ran out - I won't come back to try again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900