INNER JOIN查询一边可以为空 [英] INNER JOIN Query where one side can be Null

问题描述

嗨大师，我有一个动态查询，但是T.tmpPointsMale或T.tmpPointsFemale可能是NULL或0，所以当我需要时，记录不会出现在结果中。我已经看到一些令人困惑的线索让我疲惫的大脑吸收所以需要更简单的
建议如何解决这个问题。我相信不建议使用ISNULL&我看到了关于使用UNION的建议，但不太确定我将如何在此查询中实现它。任何指导都将非常感谢

Hi Gurus, I have a dynamic query as below but T.tmpPointsMale or T.tmpPointsFemale could be NULL or 0 so the record would not be in the result when I need it to be. I've seen some really confusing threads for my tired brain to absorb so need more simple advise on how to get around this. I believe the use of ISNULL is not recommended & I saw a suggestion about using a UNION but not quite sure how I'd implement that in this query. Any guidance would be greatly appreciated

	@CplsNo int,
	@Overide int,
	@CoupleNum int NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @SQL NVARCHAR(2000), @Where NVARCHAR(1000)
    -- Insert statements for procedure here
	SET @SQL = 'SELECT T.tmpPointsMale, T.tmpPointsFemale, MAX(ISNULL(M.First_Name,'''') +'' ''+ ISNULL(M.Surname,'''')) AS Male, MAX(ISNULL(F.First_Name,'''') +'' ''+ ISNULL(F.Surname,'''')) AS Female, MAX(ISNULL(M.eMail_Address,'''')) as Adr1, MAX(ISNULL(F.eMail_Address,'''')) as Adr2, MAX(CONVERT(INT,M.Contactable)) As Mcontact, MAX(CONVERT(INT,F.Contactable)) As Fcontact
FROM tblTempPointsHistoryReport T

INNER JOIN tblCouples C ON C.MaleID = T.tmpPointsMale or C.FemaleID = T.tmpPointsFemale
INNER JOIN tblCompetitors M ON M.Competitor_Idx = T.tmpPointsMale
INNER JOIN tblCompetitors F ON F.Competitor_Idx = T.tmpPointsFemale
 
'
IF @Overide = 0
BEGIN
	SET @Where = 
		'WHERE C.YearCpl = YEAR(CURRENT_TIMESTAMP) And Len(LTRIM(RTRIM(M.eMail_Address))) + Len(LTRIM(RTRIM(F.eMail_Address))) > 0 '
			IF @CplsNo = 1
				SET @Where = @Where + 
					'And C.CoupleNumber = @CoupleNum '
END

IF @Overide = 1

BEGIN
SET @Where =
'WHERE Len（LTRIM（RTRIM（M.eMail_Address）））+ Len（LTRIM（RTRIM（F.eMail_Address））） > 0'
如果@CplsNo = 1
SET @Where = @Where +
'和C.CoupleNumber = @CoupleNum'
END

BEGIN
SET @SQL = @SQL + @Where +'GROUP BY T.tmpPointsMale，T.tmpPointsFemale'

EXEC sp_executesql @ SQL，N'@ CplsNo int，@ Overide int，@ CoupleNum int '，@ CplsNo，@ Overide，@ CoupleNum

BEGIN SET @Where = 'WHERE Len(LTRIM(RTRIM(M.eMail_Address))) + Len(LTRIM(RTRIM(F.eMail_Address))) > 0 ' IF @CplsNo = 1 SET @Where = @Where + 'And C.CoupleNumber = @CoupleNum ' END BEGIN SET @SQL = @SQL + @Where + 'GROUP BY T.tmpPointsMale, T.tmpPointsFemale' EXEC sp_executesql @SQL,N'@CplsNo int,@Overide int,@CoupleNum int',@CplsNo, @Overide, @CoupleNum

推荐答案

美好的一天，

第一步，检查T.tmpPointsMale或T .tmpPointsFemale为NULL或0

First step, check if T.tmpPointsMale or T.tmpPointsFemale are NULL or 0

仅当值不为null且不为0时，才将过滤器添加到查询中

Only if the value is not null and it is not 0, then add the filter to the query

注意！如果这不清楚并且您需要更具体的示例，那么请提供SP的完整代码，并查询以创建相关表并插入一些简单数据，以便我们能够检查SP

Note! If this is not clear and you need more specific example then please provide the full code of the SP, and queries to create the relevant table(s) and insert some simple data, so we will be able to examine the SP

这篇关于INNER JOIN查询一边可以为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！