clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811884 fiddles created (25308 in the last week).

CREATE TABLE [dbo].[Equipament]( [ID] [nvarchar](50) NOT NULL, [AL] [nvarchar](50) NULL, [LVL] [int] NULL, [IDParent] [nvarchar](50) NULL, CONSTRAINT [PK_Equipament] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[Equipament] WITH CHECK ADD CONSTRAINT [FK_Equipament_Equipament] FOREIGN KEY([IDParent]) REFERENCES [dbo].[Equipament] ([ID])
 hidden batch(es)


INSERT INTO [Equipament] ([ID] ,[AL] ,[LVL] ,[IDParent]) VALUES ('AA' ,'07BB' ,1 ,NULL), ('AB' ,'07BB' ,1 ,NULL), ('BA' ,'07BB' ,2 ,'AA'), ('BB' ,'07BB' ,2 ,'AB'), ('BC' ,'07BB' ,2 ,'AB'), ('CA' ,'07BB' ,3 ,'BB'), ('CB' ,'07BB' ,3 ,'BC'), ('CD' ,'07BB' ,3 ,'BC'), ('CE' ,'07BB' ,3 ,'AA')
9 rows affected
 hidden batch(es)


select * from [Equipament]
ID AL LVL IDParent
AA 07BB 1
AB 07BB 1
BA 07BB 2 AA
BB 07BB 2 AB
BC 07BB 2 AB
CA 07BB 3 BB
CB 07BB 3 BC
CD 07BB 3 BC
CE 07BB 3 AA
 hidden batch(es)


CREATE TABLE [dbo].[Conns]( [Conn] [nvarchar](50) NOT NULL, [IDEquip] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Ligacoes] PRIMARY KEY CLUSTERED ( [Conn] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[Conns] WITH CHECK ADD CONSTRAINT [FK_Ligacoes_Equipament] FOREIGN KEY([IDEquip]) REFERENCES [dbo].[Equipament] ([ID])
 hidden batch(es)


INSERT INTO [dbo].[Conns] ([Conn] ,[IDEquip] ) VALUES ('123A' ,'CB' ), ('123B' ,'CB' ), ('123C' ,'CB' ), ('234A' ,'CD' ), ('234B' ,'CE' )
5 rows affected
 hidden batch(es)


;WITH QUERY AS ( SELECT E.ID,E.IDParent,L.conn FROM Equipament E LEFT JOIN conns L ON E.ID=L.IDEquip WHERE E.IDParent IS NULL UNION ALL SELECT E.ID,E.IDParent,L.conn FROM Equipament E JOIN conns L ON E.ID = L.IDEquip JOIN QUERY P on P.ID = E.IDParent ) SELECT E.ID, SUM(S.Total) AS LTotal FROM Equipament E LEFT JOIN ( SELECT Q.ID, COUNT(Q.conn) AS Total FROM QUERY Q GROUP BY Q.ID ) as S ON E.ID = S.ID GROUP BY E.ID ORDER BY E.ID option (maxrecursion 0)
ID LTotal
AA 0
AB 0
BA
BB
BC
CA
CB
CD
CE 1
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)