1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
|
WITH MaRecussiveReq(NUMCONTRAT, IDCONTRAT, IDCLIENT, IDMAISONMERE, IDANNEXECONTRAT, Level)
AS
(
SELECT C.NUMCONTRAT,
C.IDCONTRAT,
CLI.IDCLIENT,
CLI.IDMAISONMERE,
A.IDANNEXECONTRAT,
0 AS Level
FROM maBdd1..CLIENT CLI
LEFT JOIN maBdd1..CONTRAT C
ON CLI.IDCLIENT = C.IDCLIENT
LEFT JOIN maBdd1..ANNEXECONTRAT A
ON C.IDCONTRAT = A.IDCONTRAT
UNION ALL
SELECT C.NUMCONTRAT,
C.IDCONTRAT,
CLI.IDCLIENT,
CLI.IDMAISONMERE,
A.IDANNEXECONTRAT,
Level + 1
FROM maBdd1..CLIENT CLI
LEFT JOIN maBdd1..CONTRAT C
ON CLI.IDCLIENT = C.IDCLIENT
LEFT JOIN maBdd1..ANNEXECONTRAT A
ON C.IDCONTRAT = A.IDCONTRAT
LEFT JOIN MaRecussiveReq AS CEV
ON CLI.IDMAISONMERE = CEV.IDCLIENT
)
--------------------------------------------------------------------
SELECT NUMCONTRAT, IDCONTRAT, IDCLIENT, IDMAISONMERE, IDANNEXECONTRAT, Level
FROM MaRecussiveReq
LEFT JOIN mabdd1..CLIENT C
ON C.IDCLIENT = MaRecussiveReq.IDMAISONMERE
OPTION (MAXRECURSION 4) |
Partager