Použitie zjednocovacieho dotazu na získanie jedného výsledku z kombinácie viacerých dotazov

V predchádzajúcej časti sú v príklade s použitím databázy Northwind skombinované údaje len z dvoch tabuliek. V zjednocovacom dotaze však môžete veľmi jednoducho skombinovať aj tri alebo viac tabuliek. V nadväznosti na predchádzajúci príklad možno budete chcieť napríklad do výstupného dotazu zahrnúť aj mená z tabuľky employees(zamestnanci). Dosiahnete to tak, že pridáte tretí dotaz a pomocou ďalšieho kľúčového slova UNION ho skombinujete s predchádzajúcimi príkazmi SQL takto:

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Keď zobrazíte výsledok v údajovom zobrazení, všetci zamestnanci budú v zozname uvedení spolu so vzorovým názvom spoločnosti, a to zrejme nie je veľmi užitočné. Ak chcete, aby pole označovalo, či je osoba interným zamestnancom (in-house) alebo patrí medzi dodávateľov (supplier) či zákazníkov (customer), môžete na miesto názvu spoločnosti vložiť pevnú hodnotu. Príkaz SQL by mal vyzerať takto:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Takto by mali vyzerať výsledky v údajovom zobrazení. Access zobrazí týchto päť vzorových záznamov:

Dotaz uvedený vyššie možno skrátiť ešte viac, keďže v zjednocovacom dotaze Access prečíta len mená z výstupných polí z prvého dotazu. Vidíte, že sme odstránili výstup z častí druhého a tretieho dotazu:

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

V zjednocovacom dotaze Accessu je možné použiť zoradenie iba raz, ale každý z dotazov je možné filtrovať samostatne. V nadväznosti na zjednocovací dotaz z predchádzajúcej časti je v tomto príklade filtrovaný každý dotaz pridaním klauzuly WHERE.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Prepnite na údajové zobrazenie a uvidíte výsledky podobné týmto:

Ak sú dotazy na zjednocovanie veľmi odlišné, môžete sa ocitnúť v situácii, keď musíte vo výstupnom poli skombinovať údaje rozličných typov údajov. Ak to urobíte, zjednocovací dotaz najčastejšie vráti výsledky v podobe typu údajov text, keďže tento typ údajov môže obsiahnuť text aj čísla.

Ak chceme pochopiť, ako to funguje, použijeme zjednocovací dotaz Product Transactions vo vzorovej databáze Northwind. Otvorte túto vzorovú databázu a potom otvorte dotaz Product Transactions v údajovom zobrazení. Posledných desať záznamov by malo sa malo podobať na tento výstup:

Povedzme, že chcete pole Quantity (Množstvo) rozdeliť na dve polia – Buy a Sell (Nákup a Predaj). Povedzme tiež, že chcete, aby sa v poli bez hodnoty nachádzala pevná nulová hodnota. Takto bude vyzerať príkaz SQL pre tento zjednocovací dotaz:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

Ak prepnete na údajové zobrazenie, posledných desať záznamov sa teraz zobrazí nasledovne:

Pokračujme v tomto príklade – čo ak sa rozhodnete, že polia s nulovou hodnotou majú byť prázdne? Môžete upraviť príkaz SQL pridaním kľúčového slova Null a namiesto nuly sa zobrazí prázdne pole, ako je to v nasledujúcom príklade:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Ako ste si už isto všimli, po prepnutí na údajové zobrazenie sa zobrazil neočakávaný výsledok. Každé pole v stĺpci Buy (Nákup) je prázdne:

Stalo sa to preto, lebo Access určuje typy údajov polí z prvého dotazu. V tomto príklade nie je hodnota Null číslom.

Čo sa teda stane, ak sa pokúsite vložiť prázdny reťazec pre prázdne hodnoty polí? Pri tomto pokuse by syntax SQL mohla vyzerať nasledovne:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Keď prepnete na údajové zobrazenie, zistíte, že Access načítal hodnoty stĺpca Buy (Nákup), ale konvertoval ich na text. Viete, že ide o textové hodnoty, pretože v údajovom zobrazení sú zarovnané doľava. Prázdny reťazec v prvom dotaze nie je číslo, preto sa zobrazia takéto výsledky. Taktiež si všimnite, že hodnoty stĺpca Sell (Predaj) sú tiež konvertované na text, pretože záznamy o nákupe obsahujú prázdny reťazec.

Ako sa teda vyrieši tento hlavolam?

Riešením je vynútiť, aby dotaz predpokladal, že hodnota poľa bude číslo. Môžete to dosiahnuť pomocou tohto výrazu:

IIf(False, 0, Null)

Hodnota podmienky, ktorá sa kontroluje, False (Nepravda), nikdy nebude mať hodnotuTrue (Pravda), pretože výraz vždy vráti hodnotu Null. Access aj napriek tomu vyhodnotí obe možnosti výstupu a rozhodne, či bude výstup číselná hodnota alebo hodnota Null.

Takýmto spôsobom môžeme použiť tento výraz v našom funkčnom príklade:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Všimnite si, že nie je potrebné upraviť druhý dotaz.

Ak prepnete na údajové zobrazenie, zobrazí sa požadovaný výsledok:

Alternatívnou metódou, pomocou ktorej dosiahnete rovnaký výsledok, je vložiť pred dotazy v zjednocovacom dotaze ešte ďalší dotaz:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

Access pre každé pole vráti pevné hodnoty definovaného typu údajov. Samozrejme, že nechcete, aby výstup tohto dotazu zasahoval do výsledkov, takže trik, ktorému sa vyhnúť, je zahrnúť klauzulu WHERE na hodnotu False:

WHERE False

Ide o malý trik, keďže hodnota bude vždy nepravdivá a dotaz potom nevráti nič. Skombinovaním tohto príkazu s existujúcou syntaxou SQL dosiahneme dokončený príkaz, ako je ten nasledujúci:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Špeciálnym prípadom pri zjednocovacom dotaze je skombinovanie množiny záznamov s jedným záznamom, ktorý obsahuje súčet jedného alebo viacerých polí.

Tento príklad môžete vytvoriť vo vzorovej databáze Northwind na znázornenie toho, ako získať súčet v zjednocovacom dotaze.

1. Ak chcete zobraziť nákup pív (Product ID=34 v databáze Northwind), vytvorte nový jednoduchý dotaz pomocou nasledovnej syntaxe SQL:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. Prepnite na údajové zobrazenie a mali by ste vidieť štyri nákupy:

   Date Received	Quantity
   22.1.2006	100
   22.1.2006	60
   4.4.2006	50
   5.4.2006	300

3. Ak chcete získať súčet, vytvorte jednoduchý agregačný dotaz pomocou nasledujúcej syntaxe SQL:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. Prepnite na údajové zobrazenie a mali by ste vidieť iba jeden záznam:

   MaxOfDate Received	SumOfQuantity
   5.4.2006	510

5. Skombinovaním týchto dvoch dotazov v zjednocovacom dotaze pridáte záznam so súčtom množstva k záznamom o nákupe:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. Prepnite na údajové zobrazenie. Mali by ste vidieť štyri nákupy so súčtom každého nákupu a následne záznam so súčtom množstva:

   Date Received	Quantity
   22.1.2006	60
   22.1.2006	100
   4.4.2006	50
   5.4.2006	300
   5.4.2006	510

Tieto kroky pokrývajú základné informácie o pridávaní súčtov do zjednocovacieho dotazu. Možno budete chcieť zahrnúť do oboch dotazov pevné hodnoty, ako napríklad Detail (Podrobnosti) a Total (Súčet), a tak vizuálne odlíšiť záznam so súčtom od ostatných záznamov. Používanie pevných hodnôt si môžete prezrieť v časti Skombinovanie troch alebo viacerých tabuliek či dotazov v zjednocovacom dotaze.

Zjednocovacie záznamy v Accesse predvolene zahŕňajú iba rozdielne záznamy. Ale čo v prípade, že chcete zahrnúť všetky záznamy? Môže vám pomôcť ďalší príklad.

V predchádzajúcej časti sme vám ukázali, ako vytvoriť súčet v zjednocovacom dotaze. Upravte tento zjednocovací dotaz SQL tak, aby zahŕňal Product ID= 48 (ID Produktu= 48):

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Keď prepnete na údajové zobrazenie, zobrazí sa (v istom zmysle) zavádzajúci výsledok:

Jeden záznam samozrejme nevracia dvakrát súčet množstva.

Takýto výsledok sa zobrazí preto, lebo v jeden deň sa dvakrát predalo rovnaké množstvo čokolád – ako je to zaznamenané v tabuľke Purchase Order Details (Podrobnosti nákupnej objednávky). Tu je výsledok jednoduchého výberového dotazu zobrazujúci obidva záznamy vzorovej databázy Northwind:

Môžete vidieť, že v predchádzajúcom zjednocovacom dotaze pole Purchase Order ID (ID nákupnej objednávky) nie je zahrnuté a dané dve polia netvoria dva odlišné záznamy.

Ak chcete zahrnúť všetky záznamy, použite v príkaze SQL namiesto kľúčového slova UNION kľúčové slovo UNION ALL. S najväčšou pravdepodobnosťou to bude mať vplyv na zoradenie výsledkov, takže možno budete chcieť pridať klauzulu ORDER BY na určenie spôsobu zoradenia. Tu je upravená syntax SQL vytvorená na základe predchádzajúceho príkladu:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Prepnite na údajové zobrazenie. Okrem súčtu (ako pri poslednom zázname) by sa mali zobraziť aj všetky podrobnosti:

Zjednocovací dotaz zvykne bežne slúžiť ako zdroj záznamov pre rozbaľovacie pole vo formulári. V takomto rozbaľovacom poli môžete vybrať hodnotu na filtrovanie záznamov formulára. Príkladom môže byť filtrovanie záznamov zamestnancov podľa ich mesta.

Ak chcete vedieť, ako to funguje, tu je ďalší príklad, ktorý môžete vytvoriť vo vzorovej databáze Northwind na znázornenie tohto scenára.

1. Vytvorte jednoduchý výberový dotaz pomocou syntaxe SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. Prepnite na údajové zobrazenie. Mali by sa zobraziť nasledujúce výsledky:

   City	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Pohľad na tieto výsledky ale pre vás zrejme nemá vysokú hodnotu. Rozbaľte však dotaz a transformujte ho na zjednocovací dotaz použitím nasledujúcej syntaxe SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. Prepnite na údajové zobrazenie. Mali by sa zobraziť nasledujúce výsledky:

   City	Filter
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access vykoná zjednotenie deviatich (predtým zobrazených) záznamov pomocou pevných hodnôt polí <All> a "*".

   Keďže táto zjednocovacia klauzula neobsahuje kľúčové slovo UNION ALL, Access vráti len odlišné záznamy, čo znamená, že každé mesto je vrátené len raz s pevnými identickými hodnotami.

5. Teraz, keď je zjednocovací dotaz dokončený a zobrazuje názov každého mesta len raz, pričom obsahuje aj možnosť, ktorá efektívne vyberie všetky mestá, môžete tento dotaz použiť ako zdroj záznamov pre rozbaľovacie pole vo formulári. S použitím tohto konkrétneho príkladu ako modelu by ste mohli vytvoriť rozbaľovacie pole vo formulári, nastaviť tento dotaz ako zdroj záznamov, nastaviť vlastnosť Šírka stĺpca vo Filtri stĺpca na hodnotu 0 (nula), ak ho chcete skryť vizuálne, a potom nastaviť vlastnosť Viazaný stĺpec na hodnotu 1 ako označenie indexu druhého stĺpca. Do vlastnosti Filter samotného formulára môžete potom pridať kód (podobný nasledujúcemu kódu) na aktiváciu filtra formulára pomocou hodnoty toho, čo bolo vybraté v rozbaľovacom poli:

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   Používateľ formulára môže potom filtrovať záznamy formulára pre konkrétny názov mesta alebo vybrať možnosť <All> a vytvoriť zoznam všetkých záznamov pre všetky mestá.

Na začiatok stránky