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