如何通过空表上的内连接来修复空字段 [英] how to retieve empty fields by inner join on empty table

问题描述

USE [HRRecruitment]
GO
/****** Object:  StoredProcedure [dbo].[GenerateReport]    Script Date: 04/05/2015 21:30:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GenerateReport]
(
@StartDate DATE,
 @EndDate DATE,
 @Email nvarchar(40)
 )
as
begin
Declare @start VarChar, @end VarChar

If not exists(select * from tblWorkExpDetails where Email=@Email)
begin	
select 
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd 
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
else if not exists(select * from tblFresherDetails where Email=@Email)
begin
select 
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd 
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
else
begin
select 
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd 
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
end;

推荐答案

使用左连接而不是内连接，您将从两者获取数据连接的两侧，而不是它们匹配时。这样，如果表是空的，你将得到null，而不是没有行。

Use "left join" rather than "inner join" and you'll get data from both sides of the join regardless rather than when they match. That way if the table is empty you'll get "null"s back rather than no rows at all.

这篇关于如何通过空表上的内连接来修复空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！