Pages

Friday, March 24, 2017

CASE STATEMENT IN WHERE CLAUSE USING SELF JOIN

Sometimes it's possible that single table contains multiple logic. Generally we will use the self join to achieve the result but sometimes it's possible based on the some condition you need to join the self table. Is it possible in Single Query ? 

 Demo
/****** Object:  Table [dbo].[CircularRef]    Script Date: 3/24/2017 3:13:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CircularRef](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [DisplayName] [varchar](50) NULL,
 [ParentID] [int] NULL,
 [ContainsValue] [varchar](50) NULL,
 [Notes] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

  -- Test Data
  /*

  ID DisplayName  ParentID ContainsValue
   1 BigBoss      NULL     Yes
   2 Boss      1         NULL
   3 Employee  1         Yes
   4 Independent  NULL     NULL

  */

-- CASE STATEMENT IN WHERE CLAUSE
SELECT CR1.* 
FROM [CircularRef] CR1 Inner join [dbo].[CircularRef] CR2 WITH (nolock)
 ON CR1.ID = CR2.ID
LEFT OUTER JOIN [CircularRef] AS CR3 WITH (nolock)
ON CASE WHEN CR1.ContainsValue IS NOT NULL THEN CR2.ID
     WHEN CR2.ParentID IS NOT NULL THEN CR2.ParentID
    ELSE CR2.ID END = CR3.ID
where CR1.ID=2 --- Change the Value and you can see the result

No comments:

Post a Comment