TSQL rekurzivno

Imam 2 tabeli.
V tabeli T1 so imena po šifrah. V T2 pa šifre s podrejenimi šiframi T1.

Recimo:

T1.Šifra	T1.Ime
1		A
2		B
3		C
4		D

T2.PodrejenaŠifra	T2.količina
1			10
3			20

Prvo:
Kako narediti izpis, da bo pri iskanju recimo where T1.Ime like 'A' izpislo vse rezultate T2 z podrejenimi šiframi in imeni teh šifr. Recimo 1 = A, 3 = C

Drugo:
Kako izpisati rekurzivno za vse šifre, dokler T2.podrejenašifra ni null?
Torej da izpiše vse vrstite z rezultati. Pa po možnosti z omejitvijo rekurzivnosti do 5 globine.

Pa recimo da so table že linkane na skrito T2 šifro vrstice.

A kdo ve recimo zakaj tale zadeva na vrže vseh rezultatov ven?
Če v UNION ALL select izbrišem

INNER JOIN [NFakP] ON [MaticniPodatki].[MpSifra] = [NFakP].[NFaPSifra]

potem vrne pravilno število rezultatov.
Je problem INNER JOIN v rekurzivnem selectu?
Če zbrišem join NFakP ter v select dam "NULL AS [Enota]" ter vse ostale od NFakP mi vrne vse rezultate, samo NFakP je potem null.
Torej če ni povezave med [NFakP] in [MaticniPodatki] (NFakP = null) potem zaradi INNER JOIN ne vrne rezultata?

WITH [FilteredSource] AS (
SELECT	[MaticniPodatki].[MpSifra] AS [Šifra],
	[MaticniPodatki].[MpNaziv] AS [Naziv],
	[MaticniPodatki].[MpDoNaziv] AS [Dolgi naziv],
	[MaticniPodatki].[MpOznKlas] AS [Klasifikacija],
	[MaticniPodatki].[MpRisbaFName] AS [Datoteka],
	[MaticniPodatki].[MpStatus] AS [MpStatus],
	[NFakP].[NFaPEM] AS [Enota],
	[NFakP].[NFaPZnesekSkupaj] AS [Znesek z DDV],
	[NFakP].[NFaPZnesekDav] AS [DDV],
	[NFakP].[NFaPKol] AS [Količina],
	[Kosovnica].[KoNadSifMp] AS [KoNadSifMp],
	[Kosovnica].[KoPodSifMp] AS [KoPodSifMp],
	[Kosovnica].[KoKolMateriala] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [KoVarianta],
	[Kosovnica].[KoVariantaPod] AS [KoVariantaPod]
FROM [MaticniPodatki]
	FULL JOIN [Kosovnica] ON [MaticniPodatki].[MpSifra] = [Kosovnica].[KoPodSifMp]
	FULL JOIN [NFakP] ON [MaticniPodatki].[MpSifra] = [NFakP].[NFaPSifra]
WHERE	[Kosovnica].[KoVarianta] = '21354'
UNION ALL
	SELECT	[MaticniPodatki].[MpSifra] AS [Šifra],
	[MaticniPodatki].[MpNaziv] AS [Naziv],
	[MaticniPodatki].[MpDoNaziv] AS [Dolgi naziv],
	[MaticniPodatki].[MpOznKlas] AS [Klasifikacija],
	[MaticniPodatki].[MpRisbaFName] AS [Datoteka],
	[MaticniPodatki].[MpStatus] AS [MpStatus],
	[NFakP].[NFaPEM] AS [Enota],
	[NFakP].[NFaPZnesekSkupaj] AS [Znesek z DDV],
	[NFakP].[NFaPZnesekDav] AS [DDV],
	[NFakP].[NFaPKol] AS [Količina],
	[Kosovnica].[KoNadSifMp] AS [KoNadSifMp],
	[Kosovnica].[KoPodSifMp] AS [KoPodSifMp],
	[Kosovnica].[KoKolMateriala] * [FilteredSource].[Kos.] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [KoVarianta],
	[Kosovnica].[KoVariantaPod] AS [KoVariantaPod]
	FROM [MaticniPodatki]
	INNER JOIN [Kosovnica] ON [MaticniPodatki].[MpSifra] = [Kosovnica].[KoPodSifMp]
	INNER JOIN [NFakP] ON [MaticniPodatki].[MpSifra] = [NFakP].[NFaPSifra]
	INNER JOIN [FilteredSource] ON [FilteredSource].[KoPodSifMp] = [Kosovnica].[KoNadSifMp]
	WHERE [Kosovnica].[KoVarianta] = [FilteredSource].[KoVariantaPod]
)
SELECT TOP 500	[FilteredSource].[Šifra] AS [Šifra],
	[FilteredSource].[Klasifikacija] AS [Klasifikacija],
	[FilteredSource].[Naziv] AS [Naziv],
	[FilteredSource].[Dolgi naziv] AS [Dolgi naziv],
	[FilteredSource].[Kos.] AS [Kos.],
	[FilteredSource].[Enota] AS [Enota],
	[FilteredSource].[KoNadSifMp] AS [KoNadSifMp],
	[FilteredSource].[KoPodSifMp] AS [KoPodSifMp]
FROM [FilteredSource]
GROUP BY
	[FilteredSource].[Šifra],
	[FilteredSource].[Naziv],
	[FilteredSource].[Dolgi naziv],
	[FilteredSource].[Klasifikacija],
	[FilteredSource].[Datoteka],
	[FilteredSource].[KoNadSifMp],
	[FilteredSource].[KoPodSifMp],
	[FilteredSource].[Kos.],
	[FilteredSource].[KoVarianta],
	[FilteredSource].[KoVariantaPod]
ORDER BY
	[KoNadSifMp] ASC
;

Ne dela to v rekurzivnem načinu...
Je možno na kakšen drug način narediti rekurzivnost, tako da vežem [MaticniPodatki].[MpSifra] na [Kosovnica].[KoVariantaPod] ?

Oz. sej ne vem ane, a je ta podrejena šifra enaka šifri iz prve tabele, al kako imaš to zrihtano?

Ne vem potem, kaj misliš s tem rekurzivno. Lahko, da si narobe predstavljam, kako so te stvari povezane med sabo. Vem približno, kaj je rekurzija, ne vem pa, zakaj bi moral komplicirat, če hočeš samo izpisovat neke podatke..

Sej lahko, da brcam v temo in maš nekaj ful zakompliciranega, jaz pa tu bolj po noobovsko pametujem. Ampak če želiš, lahko kaj poskusim, ker se zadnje čase spet malo igram s programiranjem. :)

Za UNION ALL se začne rekurzivni select. To pomeni da dela INNER JOIN [FilteredSource] ON [FilteredSource].[KoPodSifMp] = [Kosovnica].[KoNadSifMp] dokler select ne vrne več rezultatov.

Torej je možno narediti rekurzivnost ekskluzivno na spodnjem selectu?
Torej moral bi uporabit tabelo [Kosovnica] kjer se [KoVariantaPod] sklicuje na [KoVarianta]...
in potem to združit s spodnjo tabelo.
Torej te kolone:

	[Kosovnica].[KoNadSifMp] AS [KoNadSifMp],
	[Kosovnica].[KoPodSifMp] AS [KoPodSifMp],
	[Kosovnica].[KoKolMateriala] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [KoVarianta],
	[Kosovnica].[KoVariantaPod] AS [KoVariantaPod]

mora združit z:

	[FilteredSource].[Šifra] AS [Šifra],
	[FilteredSource].[Klasifikacija] AS [Klasifikacija],
	[FilteredSource].[Naziv] AS [Naziv],
	[FilteredSource].[Dolgi naziv] AS [Dolgi naziv],
	[FilteredSource].[Enota] AS [Enota]

in narediti ujem na [Šifra] ter [KoPodSifMp]

WITH [FilteredSource] AS (
SELECT	[MaticniPodatki].[MpSifra] AS [Šifra],
	[MaticniPodatki].[MpNaziv] AS [Naziv],
	[MaticniPodatki].[MpDoNaziv] AS [Dolgi naziv],
	[MaticniPodatki].[MpOznKlas] AS [Klasifikacija],
	[MaticniPodatki].[MpRisbaFName] AS [Datoteka],
	[MaticniPodatki].[MpStatus] AS [MpStatus],
	[NFakP].[NFaPEM] AS [Enota],
	[NFakP].[NFaPZnesekSkupaj] AS [Znesek z DDV],
	[NFakP].[NFaPZnesekDav] AS [DDV],
	[NFakP].[NFaPKol] AS [Količina]
FROM [MaticniPodatki]
	FULL JOIN [NFakP] ON [MaticniPodatki].[MpSifra] = [NFakP].[NFaPSifra]
WHERE	[Kosovnica].[KoVarianta] = '21354'
)
SELECT TOP 500	[FilteredSource].[Šifra] AS [Šifra],
	[FilteredSource].[Klasifikacija] AS [Klasifikacija],
	[FilteredSource].[Naziv] AS [Naziv],
	[FilteredSource].[Dolgi naziv] AS [Dolgi naziv],
	[FilteredSource].[Enota] AS [Enota]
FROM [FilteredSource]
GROUP BY
	[FilteredSource].[Šifra],
	[FilteredSource].[Naziv],
	[FilteredSource].[Dolgi naziv],
	[FilteredSource].[Klasifikacija],
	[FilteredSource].[Datoteka]
ORDER BY
	[KoNadSifMp] ASC
;

Rekurzivni del je tale, ki vrže tabelo:

WITH [Recursion] AS (
SELECT
	[Kosovnica].[KoNadSifMp] AS [KoNadSifMp],
	[Kosovnica].[KoPodSifMp] AS [KoPodSifMp],
	[Kosovnica].[KoKolMateriala] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [KoVarianta],
	[Kosovnica].[KoVariantaPod] AS [KoVariantaPod]
FROM [Kosovnica]
WHERE	[Kosovnica].[KoVarianta] = '21354'
UNION ALL
	SELECT
	[Kosovnica].[KoNadSifMp] AS [KoNadSifMp],
	[Kosovnica].[KoPodSifMp] AS [KoPodSifMp],
	[Kosovnica].[KoKolMateriala] * [FilteredSource].[Kos.] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [KoVarianta],
	[Kosovnica].[KoVariantaPod] AS [KoVariantaPod]
	FROM [Recursion] AS [RecursionParent]
	INNER JOIN [Recursion] ON [Recursion].[KoPodSifMp] = [RecursionParent].[KoNadSifMp]
	WHERE [RecursionParent].[KoVarianta] = [Recursion].[KoVariantaPod]
)
SELECT * FROM [Recursion]

In to tabelo bi moral vstavit v prvi select znotraj:

WITH [FilteredSource] AS (
SELECT	[MaticniPodatki].[MpSifra] AS [Šifra],
	[MaticniPodatki].[MpNaziv] AS [Naziv],
	[MaticniPodatki].[MpDoNaziv] AS [Dolgi naziv],
	[MaticniPodatki].[MpOznKlas] AS [Klasifikacija],
	[MaticniPodatki].[MpRisbaFName] AS [Datoteka],
	[MaticniPodatki].[MpStatus] AS [MpStatus],
	[NFakP].[NFaPEM] AS [Enota],
	[NFakP].[NFaPZnesekSkupaj] AS [Znesek z DDV],
	[NFakP].[NFaPZnesekDav] AS [DDV],
	[NFakP].[NFaPKol] AS [Količina]
FROM [MaticniPodatki]
	FULL JOIN [NFakP] ON [MaticniPodatki].[MpSifra] = [NFakP].[NFaPSifra]
WHERE	[Kosovnica].[KoVarianta] = '21354'
)
SELECT TOP 500	[FilteredSource].[Šifra] AS [Šifra],
	[FilteredSource].[Klasifikacija] AS [Klasifikacija],
	[FilteredSource].[Naziv] AS [Naziv],
	[FilteredSource].[Dolgi naziv] AS [Dolgi naziv],
	[FilteredSource].[Enota] AS [Enota]
FROM [FilteredSource]
GROUP BY
	[FilteredSource].[Šifra],
	[FilteredSource].[Naziv],
	[FilteredSource].[Dolgi naziv],
	[FilteredSource].[Klasifikacija],
	[FilteredSource].[Datoteka]
ORDER BY
	[KoNadSifMp] ASC
;

Kako se to naredi?

smacker je 11. feb 2020 ob 22:00 izjavil:

Tukaj je ponujen solution za recursive left join, tako da narediš nov CTE: https://forums.asp.net/t/1705698.aspx?C...
Podobno tudi tu, samo da da nov view namesto CTE: http://coding-art.blogspot.com/2013/12/...

Hm tam uporabi:
WITH [Recursion] AS (), [FilteredSource] AS () SELECT * ...

Zanimivo. Bomo sprobal...