Mitme päringu ühendamine ühe tulemuse saamiseks ühispäringu abil

Eelmise jaotise näidetes, kus kasutati Northwindi andmebaasi, kombineeriti ainult kahe tabeli andmed. Ühispäringus saate aga hõlpsasti kombineerida ka kolme või enama tabeli andmeid. Näiteks saaksite eelmises näites kerge vaevaga lisada päringu väljundisse ka töötajate nimed. Selleks lisage kolmas päring ja kombineerige eelmine SQL-lause täiendava UNION-võtmesõnaga, nagu siin näidatud:

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];

Kui vaatate tulemeid andmelehevaates, on kõik töötajad ära toodud näidisettevõtte nimega, mis pole tõenäoliselt väga abiks. Kui soovite, et see väli näitaks, kas inimene on teie enda ettevõtte töötaja, hankija töötaja või kliendi töötaja, saate ettevõtte nime asemel kasutada fikseeritud väärtust. SQL näeks sel juhul välja umbes selline:

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];

Siin näete, kuidas tulem andmelehevaates kuvatakse. Access kuvab need viis näidiskirjet:

Kuna Access loeb nimed ainult ühispäringu esimese päringu väljundiväljadelt, saab ülalpool toodud päringut veelgi rohkem lihtsustada. Siin näete, et oleme teise ja kolmanda päringu jaotistest väljundi eemaldanud:

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];

Accessi ühispäringus on järjestamine lubatud ainult üks kord, kuid iga päringut saab omaette filtreerida. Võttes aluseks eelmises jaotises kirjeldatud ühispäringu, on siin näide selle kohta, kuidas oleme iga päringu WHERE-klausli lisamise abil filtreerinud.

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];

Kui avate andmelehevaate, peaksite nägema sarnaseid tulemeid:

Kui ühendatavad päringud on väga erinevad, võib tekkida olukord, kus väljundväljal tuleb kombineerida eri andmetüüpidega andmeid. Sel juhul tagastab ühispäring tulemid enamasti teksti andmetüübiga, kuna see andmetüüp saab hõlmata nii teksti kui ka arve.

Selle paremaks selgitamiseks kasutame Northwindi näidisandmebaas ühispäringut Product Transactions (Tootetehingud). Avage see näidisandmebaas ja seejärel avage päring „Product Transactions“ andmelehevaates. Kümme viimast kirjet peaksid olema umbes järgmised:

Oletame, et soovite välja Kogus tükeldada kaheks : Osta ja Müü. Oletame ka, et soovite ilma väärtuseta välja jaoks kasutada fikseeritud nullväärtust. Sql otsib seda ühispäringut järgmiselt.

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; 

Kui avate andmelehevaate, näete, et viimased kümme kirjet kuvatakse nüüd nii:

Mida teha siis, kui soovite, et nullväärtusega väljad oleksid tühjad? Sql-i saate muuta nii, et see ei kuvaks nulli asemel midagi, lisades märksõna Null järgmiselt:

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;

Ent nagu andmelehevaatest näha, annab see mõnevõrra ootamatu tulemuse. Veerus Buy (Ost) on nüüd kõik väljad tühjad:

Põhjus on selles et Access määratleb väljade andmetüübid esimese päringu põhjal. Käesolevas näites pole märksõna Null arv.

Mis siis juhtub, kui proovite lisada väljade tühja väärtuse korral tühja stringi? Selle katse SQL võib välja näha umbes järgmine:

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;

Andmelehevaates näete, et Access toob veeru Buy (Ost) väärtused, kuid teisendab need väärtused tekstiks. Seda, et tegemist on tekstväärtustega, näitab see, et need on andmelehevaates vasakjoondatud. Neid tulemeid näete seetõttu, et esimese päringu tühi string ei ole arv. Samuti märkate kindlasti, et ka veeru Sell (Müük) väärtused on teisendatud tekstiks, kuna ostukirjed sisaldavad tühja stringi.

Kuidas seda mõistatust lahendada?

Lahendus on panna päring eeldama, et välja väärtus on arv. Seda saab teha järgmise avaldisega:

IIf(False, 0, Null)

Kuna kontrollitav tingimus False (Väär) pole kunagi True (Tõene), tagastab avaldis alati tühiväärtuse Null, ent Access väärtustab siiski mõlemad väljundivariandid ja otsustab, kas väljund on arvuline või tühi (Null).

Selle artikli töönäites saame avaldist kasutada nii:

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õtke arvesse, et teist päringut pole vaja muuta.

Kui aktiveerite andmelehevaate, näetegi nüüd soovitud tulemust:

Teine võimalus sama tulemuse saamiseks on lisada ühispäringu päringute ette veel üks päring:

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

Iga välja korral tagastab Access fikseeritud väärtused, millel on teie määratletud andmetüüp. Kuna te ei soovi, et selle päringu väljund hakkaks tulemusi segama, tuleks siin lisada WHERE-klausel väärtusele False (Väär):

WHERE False

Kuna see väärtus on alati väär, ei tagasta see päring midagi. Selle lause kombineerimine olemasoleva SQL-iga annabki meile tulemuseks lõpliku lause:

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;

Ühispäringus saab kombineerida kirjekomplekti ühe kirjega, mis sisaldab ühe või mitme välja summat.

Siin on järgmine näide, mille saate ise Northwindi näidisandmebaasis luua ja mis annab teile aimu sellest, kuidas ühispäringus summat leida.

1. Looge uus lihtpäring õlleostude vaatamiseks (Northwindi andmebaasis Product ID=34), kasutades järgmist SQL-i süntaksit:

   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. Aktiveerige andmelehevaade. Peaksite nägema nelja ostu:

   Date Received (Vastu võetud)	Quantity (Kogus)
   22.01.2006	100
   22.01.2006	60
   04.04.2006	50
   05.04.2006	300

3. Summa leidmiseks looge lihtne kokkuvõttepäring, kasutades järgmist SQL-i:

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

4. Aktiveerige andmelehevaade. Peaksite nägema ainult ühte kirjet:

   MaxOfDate Received	SumOfQuantity
   05.04.2006	510

5. Kombineerige need kaks päringut ühispäringuks, et lisada kogukogusega kirje ostukirjetele:

   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. Aktiveerige andmelehevaade. Peaksite nägema nelja ostu; iga ostu summale peaks järgnema koguse kokkuvõttega kirje:

   Date Received (Vastu võetud)	Quantity (Kogus)
   22.01.2006	60
   22.01.2006	100
   04.04.2006	50
   05.04.2006	300
   05.04.2006	510

Kokkuvõtete või summade lisamine ühispäringusse käibki põhimõtteliselt nii. Kui soovite kokkuvõttekirje muudest kirjetest visuaalselt eristada, võite mõlemasse päringusse kaasata fikseeritud väärtused, näiteks „Detail“ (Üksikasi) ja „Total“ (Kokkuvõte). Fikseeritud väärtuste kasutamisest leiate ülevaate jaotisest Kolme või enama tabeli või päringu kombineerimine ühispäringuks.

Vaikimisi kaasatakse Accessis ühispäringutesse ainult eristatavad kirjed. Mida aga teha siis, kui soovite kaasata kõik kirjed? Siin võib abil olla teisest näitest.

Eelmises jaotises näitasime teile, kuidas luua ühispäringus kokkuvõte. Muutke selle ühispäringu SQL-i, et kaasata Product ID= 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];

Aktiveerige andmelehevaade. Peaksite nägema mõnevõrra eksitavat tulemust:

Muidugi ei tagasta üks kirje kokkuvõttes kahekordset kogust.

Põhjus, miks seda tulemust näete, on see, et ühel päeval müüdi sama šokolaadikogus kaks korda, nagu on kirjas tabelis Ostutellimuse üksikasjad. Siin on lihtne valikupäringu tulem, mis näitab mõlemat Northwindi näidisandmebaasi kirjet:

Nagu näete, ei sisalda varem loodud ühispäring välja „Purchase Order ID“ ning need kaks välja ei moodusta kahte eristatavat kirjet.

Kui soovite kaasata kõik kirjed, kasutage oma SQL-is võtmesõna UNION asemel võtmesõna UNION ALL. Kuna see mõjutab arvatavasti tulemite sortimist, võiksite sortimisjärjestuse määratlemiseks kaasa ka klausli ORDER BY. Eelmisel näitel põhinev muudetud SQL on järgmine:

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];

Aktiveerige andmelehevaade. Peaksite nägema kõiki üksikasju ja lisaks viimase kirjena ka kokkuvõtet:

Sageli kasutatakse ühispäringuid vormil liitboksi juhtelemendi kirjeallikana. Selle liitboksi kaudu saate valida vormi kirjete filtreerimiseks soovitud väärtuse. Näiteks saate töötajakirjeid linna alusel filtreerida.

Kui soovite näha, kuidas see töötab, on siin järgmine näide, mille saate selle stsenaariumi illustreerimiseks ise Northwindi näidisandmebaasis luua.

1. Looge lihtne valikupäring, kasutades seda SQL-i süntaksit:

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

2. Aktiveerige andmelehevaade. Peaksite nägema järgmist tulemust:

   City (Linn)	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. See pilt ei pruugi teile anda eriti väärtuslikku teavet. Päringut saate aga laiendada ja teisendada selle ühispäringuks, kasutades järgmist SQL-i:

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

4. Aktiveerige andmelehevaade. Peaksite nägema järgmist tulemust:

   City (Linn)	Filter
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access liidab kõik üheksa varem kuvatud kirjet, kasutades fikseeritud väljaväärtusi <All> ja „*“.

   Kuna see ühendamisklausel ei sisalda võtmesõna UNION ALL, tagastab Access ainult eristatavad kirjed, mis tähendab, et iga linn tagastatakse fikseeritud samaste väärtuste korral ainult üks kord.

5. Nüüd, kui teil on olemas lõpetatud ühispäring, kus iga linna nimi kuvatakse ainult üks kord ja mis sisaldab ka kõigi linnade valimise võimalust, saategi seda päringut kasutada vormi liitboksi kirjeallikana. Seda konkreetset näidet mudelina kasutades võiksite vormil luua liitboksi juhtelemendi, määrata selle päringu juhtelemendi kirjeallikaks, määrata veeru Filter atribuudi Column Width (Veeru laius) väärtuseks 0 (null), et see visuaalselt peita, ja seejärel määrata atribuudi Bound Column (Seotud veerg) väärtuseks 1, et osutada teise veeru indeksile. Vormi enda atribuudis Filter saate seejärel lisada näiteks järgmise koodi, et aktiveerida vormifilter liitboksi juhtelemendis valitud väärtuse abil:

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

   Vormi kasutaja saab seejärel vormikirjeid filtreerida, et kuvada kindel linn, või valida <All> (Kõik) kõigi linnade kirjete loetlemiseks.

Lehe algusse