In employee master, if any position vacant based on crews

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 

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 

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

Crew-A
IT Manager
IT Manager
IT Assistant
Programmer
Crew-B
IT Head
IT Team Lead
IT Team Lead 

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 

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');

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