-- Uvazujte databazu OsobaVztah

-- a) Vratte id deti a id ich rodicov do dvoch stlpcov.

-- Tri riesenia:

-- Riesenie A:

USE OsobaVztah;
SELECT id, matka FROM Osoba WHERE matka is not null
union all
SELECT id, otec FROM Osoba WHERE otec is not null
ORDER BY id

-- Riesenie B:

GO
WITH DieRod(idD, idR)
AS(
SELECT id, matka FROM Osoba WHERE matka is not null
union all
SELECT id, otec FROM Osoba WHERE otec is not null
)
SELECT * FROM DieRod ORDER BY idD

-- Riesenie C:

SELECT D.id, R.id FROM Osoba AS D
 JOIN Osoba R
      ON D.id IN(R.matka, R.otec)
      --  ON R.id IN(D.matka, D.otec)

-- b) Vratte id, mena a priezviska deti a ich rodicov.

-- Riesenie bez CTE:

SELECT
Dieta.id AS 'Id dietata', Dieta.meno AS 'Meno dietata', Dieta.priezvisko AS 'Priez. dietata',
Rodic.id AS 'Id rodica', Rodic.meno AS 'Meno rodica', Rodic.priezvisko AS 'Priez. rodica'
    FROM Osoba AS Dieta
JOIN Osoba AS Rodic
  --NO-- ON Dieta.id IN(Rodic.matka, Rodic.otec)
         ON Rodic.id IN(Dieta.matka, Dieta.otec)

-- Riesenie pomocou CTE:

WITH DietaRodic(dietaId, rodicId) AS
(
	SELECT 
		Dieta.id, Rodic.id
	FROM 
		Osoba AS Dieta
		JOIN Osoba AS Rodic 
			ON Rodic.id IN(Dieta.matka, Dieta.otec) -- Ak som rodic, tak som matkou alebo otcom svojho dietata.
)
SELECT 
	Dieta.id AS 'Id dietata', Dieta.meno AS 'Meno dietata', Dieta.priezvisko AS 'Priez. dietata', 
	Rodic.id AS 'Id rodica', Rodic.meno AS 'Meno rodica', Rodic.priezvisko AS 'Priez. rodica' 
FROM 
	DietaRodic 
	JOIN Osoba AS Dieta 
		ON Dieta.id = DietaRodic.dietaId
	JOIN Osoba AS Rodic 
		ON Rodic.id = DietaRodic.rodicId;

GO


--===============================================================================================--
-- c) Rekurzivnou CTE vratte z tabulky Osoba mena a priezviska deti a ich predkov, vcitane poctu 
-- generacii medzi potomkom a predkom. Inak povedane vytvorte tranzitivny uzaver relacie 
-- reprezentovanou CTE z predchadzajucej ulohy. 

WITH PotomokPredok(potomokId, predokId, generacia) AS
(
	-- Baza rekurzie, v podstate riesenie predchadzajucej ulohy doplnene o pocet generacii medzi 
	-- dietatom a rodicom (1 generacia).
	SELECT 
		Dieta.id, Rodic.id, 1
	FROM 
		Osoba AS Dieta
		JOIN Osoba AS Rodic 
			ON Rodic.id IN(Dieta.matka, Dieta.otec) -- Ak som rodic, tak som matkou alebo otcom svojho dietata.
	
	UNION ALL
  
	-- Rekurzivne volanie
	SELECT 
		Dieta.id, Predok.predokId, generacia + 1 -- Pri rekurzivnom volani sa nam generacia zvysi o 1
	FROM 
		PotomokPredok AS Predok -- Rekurzivny dopyt na seba sameho
		JOIN Osoba AS Dieta 
			ON Predok.potomokId IN(Dieta.matka, Dieta.otec) -- Ak som Predok.potomokId, tak moj predok je Predok.predokId, pricom ja som matkou alebo otcom svojho dietata.
	-- Podmienka ukoncenia rekurzie tu nemusi byt, lebo tabulka Osoba ma konecny pocet riadkov. 
)
SELECT 
	Potomok.id AS 'Id potomka', Potomok.meno AS 'Meno potomka', Potomok.priezvisko AS 'Priez. potomka', 
	Predok.id AS 'Id predka', Predok.meno AS 'Meno predka', Predok.priezvisko AS 'Priez. predka',
	PotomokPredok.generacia 
FROM 
	PotomokPredok
	JOIN Osoba AS Potomok 
		ON Potomok.id = PotomokPredok.potomokId
	JOIN Osoba AS Predok 
		ON Predok.id = PotomokPredok.predokId
-- Odkomentuj riadok nizsie ak chces iba vnucata a ich starych rodicov
-- WHERE PotomokPredok.generacia = 2
ORDER BY PotomokPredok.generacia;