Simple Question on ANSI Join

Hi All,

I've simple question on the below join, What I am understanding is:

If the --1st Join do not return data it evaluates --2nd join and if this also do not return data it checks for --3rd if third returns data it will be [DDCM].[ReptColName]

If Join 1 returns data it will be [MPM1].[MiProgram] and if 2nd returns data it will be [MPM2].[MiParent]

from the business I understand that all 3 will not return data at anytime, it will one only one join which returns 

SELECT
ISNULL(	ISNULL([MPM1].[MiProgram], [MPM2].[MiParent])
      ,[DDCM].[ReptColName]
      ) AS [FCategory]
FROM R2
LEFT JOIN [DBO].[vwPrgMapping] AS [MPM1]
ON [MPM1].[Category] = [R2].[FilterLabel]       --1
LEFT JOIN [DBO].[vwParentMapping] AS [MPM2]
ON [MPM2].[Category] = [R2].[FilterLabel]       --2
LEFT JOIN [DBO].[vwDDMapping] AS [DDCM] 
ON [DDCM].[Category] = [R2].[FilterLabel]       --3		
July 6th, 2015 7:38pm

Hello,

As per your Query It does all the joins irrespective of whether it contains data or not and returns the result as based on the condition which you mentioned in Select.

Like as you mentioned

--> It checks [MPM1].[MiProgram] is it NULL --> Yes --> Checks ISNULL([MPM1].[MiProgram], [MPM2].[MiParent]) this results null or not --> Yes --> returns [DDCM].[ReptColName]

--> It checks [MPM1].[MiProgram] is it NULL --> Yes --> Checks ISNULL([MPM1].[MiProgram], [MPM2].[MiParent]) this results null or not --> NO --> returns  [MPM2].[MiParent]

--> It checks [MPM1].[MiProgram] is it NULL --> NO--> Returns [MPM1].[MiProgram]

 

Thanks

---------------------------------------------------------------------------------------------------------------

Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

Free Windows Admin Tool Kit Click here and download it now
July 6th, 2015 7:50pm

Neil, try replace IsNull() by Coalesce().
 

SELECT Coalesce (MPM1].[MiProgram], [MPM2].[MiParent], [DDCM].[ReptColName]) AS [FCategory]
from ...
July 6th, 2015 7:53pm

SELECT ISNULL( ISNULL(1, 2) ,3) AS [FCategory]  ---> result would be 1
SELECT ISNULL( ISNULL(NULL, 1) ,3) AS [FCategory] ---> result would be 2
SELECT ISNULL( ISNULL(NULL, NULL) ,3) AS [FCategory] ---> result would be 3

or you an write

SELECT Coalesce(NULL, NULL ,3) AS [FCategory]  ---> result would be 3
SELECT Coalesce(1, 2 ,3) AS [FCategory]  ---> result would be 1

I think above output will explain your doubt

Free Windows Admin Tool Kit Click here and download it now
July 6th, 2015 8:10pm

Thanks, what I am understanding is all 3 joins are happening on TABLE R2 hence which one appears first or last is irrelevant?

The precedence doesn't matters here right?  --1(join) could be placed in last and --3 could be placed first is irrelevant ?? 

FROM R2
LEFT JOIN [DBO].[vwPrgMapping] AS [MPM1]
ON [MPM1].[Category] = [R2].[FilterLabel]       --1
LEFT JOIN [DBO].[vwParentMapping] AS [MPM2]
ON [MPM2].[Category] = [R2].[FilterLabel]       --2
LEFT JOIN [DBO].[vwDDMapping] AS [DDCM] 
ON [DDCM].[Category] = [R2].[FilterLabel]       --3

 
July 6th, 2015 11:57pm

Thanks, what I am understanding is all 3 joins are happening on TABLE R2 hence which one appears first or last is irrelevant?

The precedence doesn't matters here right?  --1(join) could be placed in last and --3 could be placed first is irrelevant ?? 

FROM R2
LEFT JOIN [DBO].[vwPrgMapping] AS [MPM1]
ON [MPM1].[Category] = [R2].[FilterLabel]       --1
LEFT JOIN [DBO].[vwParentMapping] AS [MPM2]
ON [MPM2].[Category] = [R2].[FilterLabel]       --2
LEFT JOIN [DBO].[vwDDMapping] AS [DDCM] 
ON [DDCM].[Category] = [R2].[FilterLabel]       --3

Free Windows Admin Tool Kit Click here and download it now
July 7th, 2015 1:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics