Moderný reporting pracujeme s MySQL (spájanie pomocou JOIN)

 

Väčšina z PC friendly čitateľov určite vrámci databáz počula pojem SELECT-FROM-WHERE-JOIN – GROUP BY spojenie. Vzhľadom k predchádzajúcim článkom si v podstate všetko okrem klauzuly JOIN a GROUP BY dokážete dať do súvislostí.

Tak teda čo znamená pojem JOIN v SQL jazyku:

Podľa Wiki je to klauzula, založená na vymedzení referenčných polí, ktorá kombinuje záznamy medzi tabuľkami. Ľudskou rečou povedané, ak mám napríklad dve tabuľky tak pomocou JOIN spolu s vymedzaním relevantných stĺpcov (Foerign key stĺpec v primárnej tabuľke =  Primary key stĺpec v referenčnej)  program vie, ako má dané záznamy spojiť.

Ak si spomeniete na predchádzajúci článok, kde som spájal dve tabuľky takto:

SELECT dealer.Predajca, cost.* FROM cost, dealer WHERE Cost.Predajca=Dealer.ID;

Tak v novom príkaze vypustíme klauzulu WHERE a pridáme JOIN (typ spojenia). Tu sa dostávame pre mnohých do komplikovaného rozhodnutia typu: Aký typ JOIN klauzuly použiť?

V tomto bode si dovolím inšpirovať sa knihou Mistrovství v MySQL od M. Koflera, kde na str. 237 má prehľad typov JOIN klauzúl:

Nepodmienené typy vrátia všetky možné kombinácie vrámci záznamov, odlišný je však STRAIGHT_JOIN, ktorý neoptimalizuje poradie výberu dat. Zameriame sa preto na použitie podmienených typov:

V podmienených typoch záleží na poradí tabuliek. Pre pochopenie som vytvoril nasledujúce tabuľky:

Tabuľka Dealer:

Tabuľka Dovolenka:

Všimnite si, že Tabuľky Dovolenka sa odkazuje na zoznam pracovníkov z tabuľky Dealer a zároveň fakt, že Helena zatiaľ nečerpala žiadnu dovolenku a v Dovolenke máme dva neplatné záznamy s ID zamestnanca 5 a 7.

Ak vezmeme v úvahu LEFT JOIN (a zároveň NATURAL JOIN) vrámci tabuliek dealer a dovolenka, nasledujúci skript:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti všetky záznamy z tabuľky Dovolenka, ku ktorým doplní relevantné mená z tabuľky Dealer. V prípade, ak by sme v evidencii Dovolenka mali ID > 4, znamenalo by to, že tam máme zle vloženú evidenciu alebo ID zamestnanca, ktorý ešte nie je evidovaný v systéme (tabuľke Dealer).

To znamená, ak by kontent tabuľky Dovolenka vyzeral takto:

Predchádzajúci skript by vyhodil nasledujúci prehľad:

To znamená, že vrátil všetky dovolenkové záznamy, vrátane tých, ku ktorým v tabuľke Dealer nenašiel Meno (súčet hodín s prívlastkom NULL).

Čo by sa stalo, ak by sme zamenili LEFT za RIGHT JOIN:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka right join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Tento skript by zamenil tabuľky. To znamená, ku každému Menu v tabuľke Dealer by priradil súčty hodín vrámci dovolenky:

Všimnite si, že nám vrátil naviac prehľad o Helene, ktorá ešte dovolenku nečerpala a zároveň sa zbavil neidentifikovaných 13 hodín voľna. Čo sa stane ak použijem pôvodný select typ z predcházajúceho článku alebo INNER JOIN? INNER JOIN v podstate definuje niečo ako skalárny súčin údajov, medzi dvoma tabuľkami.

Znamená to, že ak niektorá z tabuliek obsahuje vrámci referencií hodnoty NULL (Helena v tabuľke Dealer a záznam 4 a 5 z tabuľky Dovolenka), tak tieto hodnoty budú ignorované pri výstupe:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka inner join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

alebo:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka, dealer where dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti nasledujúci prehľad:

Ak by sme tam chceli zanechať tých 13 hodín, museli by sme im priradiť správne ID alebo definotať ich ako nových zamestnancov. Helena sa tam objaví v prípade, že niekedy načerpá dovolenku.

Všimnite si časť GROUP BY: Tá je potrebná, aby aplikácia vedela, podľa ktorých parametrov má urobiť prehľad. Inak povedané, predchádzajúce kódy vypočítali súčet hodín z dovoleniek a rozdelili ich podľa hodnôt zo stĺpca predajca v tabuľke dealer.

Použijeme prvý skript ale bez group by:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID;

Sami vidíme, že výsledok čo sa týka počtu hodín je rovnaký. Akurát problém je v tom, že tu aplikácia zobrala prvé ID = 1, ktoré má hodnotu Peter a k nemu priradila celkový súčet hodín. Príkaz GROUP BY vytvorí prehľad všetkých Mien a k nim nasledne dohodí súčty, ktoré celkovo dajú hodnotu 82 hodin.

Poučenie: snažte sa čo najlepšie poznať svoju databázu a taktiež si vždy presne vymedzte, čo je cieľom selektu.

Tým pádom môžem konštatovať, že sme pripravení si v ďalších článkoch vytvárať selekty podľa daných požiadaviek. Taktiež sa začneme hrať (kresliť si) so schémami dashboardov.

V prípade dotazov sa neváhajte pýtať – alebo kliknite na FB odkaz dole.