Useiden kyselyjen yhdistäminen yhdeksi tulokseksi yhdistämiskyselyn avulla

Edellisen osion Northwind-tietokantaa käyttävässä esimerkissä yhdistetään tietoa vain kahdesta taulukosta. Voit kuitenkin yhdistää kolme tai enemmän taulukoita yhdistämiskyselyksi hyvin helposti. Edelliseen esimerkkiin viitaten: haluat ehkä sisällyttää myös työntekijöiden nimet kyselyn tuloksiin. Voit tehdä tämän lisäämällä kolmannen kyselyn ja yhdistämällä edelliseen SQL-lausekkeeseen ylimääräisen UNION-avainsanan tällä tavalla:

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

Kun tarkastelet tulosta taulukkonäkymässä, kaikki työntekijät luetteloidaan saman yritysnimen yhteydessä, mikä ei välttämättä ole kovin hyödyllistä. Jos haluat kentän ilmaisevan, onko joku oman yrityksen, toimittajan tai asiakasyrityksen työntekijä, voit käyttää kiinteää arvoa yrityksen nimen sijasta. SQL voi silloin näyttää tältä:

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

Tältä tulos näyttää taulukkonäkymässä. Access näyttää nämä viisi esimerkkitietuetta:

Yllä olevaa kyselyä voi pienentää ennestään, koska Access lukee tuloskenttien nimet vain yhdistämiskyselyn ensimmäisestä kyselystä. Tästä näet, että olemme poistaneet toisen ja kolmannen osakyselyn tulokset:

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

Access-yhdistämiskyselyissä järjestäminen sallitaan vain kerran, mutta jokainen kysely voidaan suodattaa erikseen. Viittauksena edellisen osan yhdistämiskyselyyn: tässä on esimerkki, jossa olemme suodattaneet jokaisen kyselyn lisäämällä WHERE-lauseen.

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

Vaihtamalla taulukkonäkymään saat tämän tapaisia tuloksia:

Jos yhdistetyt kyselyt ovat hyvin erilaisia, saatat joutua tilanteeseen, jossa tuloskenttään on yhdistettävä useita eri tietotyyppejä. Jos näin käy, yhdistämiskysely antaa tulokset tekstimuodossa, koska tämä tietotyyppi voi sisältää sekä tekstiä että lukuja.

Tämän havainnollistamiseksi käytämme Northwind-esimerkkitietokannan Tuotetapahtumat -yhdistämiskyselyä. Avaa esimerkkitietokanta ja avaa sitten Tuotetapahtumat-kysely taulukkonäkymässä. Viimeisten kymmenen tietueen pitäisi olla samankaltaisia kuin tämä tulos:

Oletetaan,että haluat jakaa Määrä-kentän kahtia: Osta ja Myy. Oletetaan myös, että haluat määrittää kiinteän nolla-arvon kentälle ilman arvoa. Tältä näyttää tämän yhdistämiskyselyn SQL:

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; 

Jos vaihdat taulukkonäkymään, näet viimeiset kymmenen näytettävää tietuetta näin:

Jatketaan samaa esimerkkiä: entä jos haluat nollan sisältävien kenttien olevan tyhjiä? Voit muokata SQL:n näyttämään tyhjää nollan sijasta käyttämällä seuraavalla tavalla avainsanaa Null:

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;

Kuitenkin, kuten olet ehkä jo huomannut vaihtaessasi taulukkonäkymään, saat nyt odottamattoman tuloksen. Osta-sarakkeen kaikki kentät poistetaan:

Tämä johtuu siitä, että Access määrittää tietotyypit ensimmäisen kyselyn kenttien mukaan. Tässä esimerkissä Null ei ole luku.

Mitä tapahtuu, jos yrität lisätä tyhjän merkkijonon kenttiin, joiden arvo on tyhjä? Tällöin SQL voi näyttää tältä:

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;

Kun siirryt taulukkonäkymään, näet, että Access hakee Osta-arvot, mutta muuttaa ne tekstiksi. Näet, että nämä ovat tekstiarvoja, koska ne tasataan taulukkonäkymässä vasemmalle. Ensimmäisen kyselyn tyhjä merkkijono ei ole luku, ja tämän takia näet nämä tulokset. Huomaat senkin, että myös Myy-arvot muunnetaan tekstimuotoon, koska ostamistietueet sisältävät tyhjän merkkijonon.

Miten tämä ongelma siis ratkaistaan?

Yksi mahdollisuus on pakottaa kysely olettamaan, että kentän arvo on luku. Tämä voidaan tehdä seuraavalla lausekkeella:

IIf(False, 0, Null)

Tarkistettava ehto, Epätosi, ei koskaan ole Tosi, joten lauseke palauttaa aina arvon Null, mutta Access arvioi silti molemmat tulosvaihtoehdot ja määrittää tulokseksi joko numeroarvon tai Null-arvon.

Näin voimme käyttää tätä lauseketta käyttöesimerkissämme:

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;

Huomaa, ettei toista kyselyä ole välttämätöntä muokata.

Jos vaihdat taulukkonäkymään, saamme haluamamme tuloksen:

Vaihtoehtoinen tapa päästä samaan tulokseen on lisätä yhdistämiskyselyn osakyselyiden eteen vielä yksi kysely:

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 palauttaa määrittelemäsi tietotyypin kiinteän arvon kullekin kentälle. Et tietenkään halua tämän kyselyn tuloksen sotkevan tuloksia, ja tämän vältät lisäämällä Epätoteen WHERE-lauseen:

WHERE False

Tämä on pikkuista huiputusta, sillä tämä on aina epätosi eikä kysely tällöin palauta mitään. Kun tämä lauseke yhdistetään aiemmin luotuun SQL:ään, saadaan seuraavanlainen kokonaislauseke:

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;

Yhdistämiskyselyn erikoistapauksessa yhdistetään joukko tietueita yhteen tietueeseen, joka sisältää yhden tai useamman kentän summan.

Tässä on toinen esimerkki, jonka voit luoda Northwind-esimerkkitietokannassa havainnollistamaan sitä, miten yhdistämiskyselyssä saadaan summa.

1. Luo uusi yksinkertainen kysely ja tarkastele sen avulla oluen ostamista (tuotetunnus 34 Northwind-tietokannassa) käyttämällä seuraavaa SQL-syntaksia:

   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. Siirtymällä taulukkonäkymään sinun pitäisi nähdä neljä ostoa:

   Vastaanottopäivämäärä	Määrä
   22.1.2006	100
   22.1.2006	60
   4.4.2006	50
   5.4.2006	300

3. Kun haluat saada summan, luo yksinkertainen yhdistävä kysely käyttämällä seuraavaa SQL:ää:

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

4. Siirtymällä taulukkonäkymään sinun pitäisi nähdä vain yksi tietue:

   MaxOfDate Received	SumOfQuantity
   5.4.2006	510

5. Yhdistämällä nämä kaksi kyselyä yhdistämiskyselyyn voit lisätä tietueen, joka sisältää ostamistietueiden määrien summan:

   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. Siirtymällä taulukkonäkymään sinun pitäisi nähdä kaikki neljä ostoa niin, että jokaisen summaa seuraa kokonaissummatietue:

   Vastaanottopäivämäärä	Määrä
   22.1.2006	60
   22.1.2006	100
   4.4.2006	50
   5.4.2006	300
   5.4.2006	510

Tässä olemme käsitelleet perustiedot summien lisäämisestä yhdistämiskyselyyn. Haluat ehkä myös sisällyttää molempiin kyselyihin kiinteitä arvoja, kuten ”Yksittäinen” ja ”Kokonaissumma” summatietueen erottamiseksi muista tietueista. Voit kerrata kiinteiden arvojen käyttöä kohdassa Kolmen tai useamman taulukon tai kyselyn yhdistäminen yhdistämiskyselyssä.

Accessin yhdistämiskyselyt sisältävät oletusarvoisesti vain erillisiä tietueita. Mutta entä jos haluat sisällyttää kaikki tietueet? Kannattaa ottaa tässä esille uusi esimerkki.

Edellisessä osassa näytimme, miten voit luoda kokonaissumman yhdistämiskyselyssä. Muokkaa tämän yhdistämiskyselyn SQL:ää niin, että siihen sisältyy tuotetunnus 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];

Siirtymällä taulukkonäkymään sinun pitäisi nähdä hieman harhaanjohtava tulos:

Yksi tietue ei tietenkään palauta kokonaismäärää kaksinkertaisena.

Näet tämän tuloksen siksi, että yhtenä päivänä sama suklaamäärä on myyty kahdesti - kuten ostotilaustietojen taulukkoon on merkitty. Tässä on yksinkertainen valintakyselytulos, joka näyttää molemmat tietueet Northwind-esimerkkitietokannasta:

Edellä mainitussa yhdistämiskyselyssä näet, että ostotilaustunnuksen kenttää ei ole mukana ja että kaksi kenttää eivät ole kaksi erillistä tietuetta.

Jos haluat sisällyttää kaikki tietueet, käytä SQL:ssäsi avainsanaa UNION ALL pelkän UNIONin sijasta. Tämä vaikuttaa todennäköisesti tulosten lajitteluun, joten voi olla järkevää sisällyttää myös ORDER BY -lause lajittelujärjestyksen määrittämiseksi. Tässä on edelliseen esimerkkiin perustuva muokattu SQL:

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

Siirtymällä taulukkonäkymään sinun pitäisi nähdä kaikki yksittäiset kohteet sekä viimeisenä tietueena kokonaissumma:

Yleinen tapa käyttää yhdistämiskyselyä on käyttää sitä tietuelähteenä lomakkeen yhdistelmäruudun ohjausobjektille. Yhdistelmäruutua käyttäen voit valita arvon lomakkeen tietueiden suodattamista varten. Voit esimerkiksi suodattaa työntekijöiden tietueet heidän kaupunkinsa mukaan.

Tarkastele tätä esimerkkiä, jonka voit luoda Northwind-esimerkkitietokannassa tämän tilanteen havainnollistamiseksi.

1. Luo yksinkertainen valintakysely tätä SQL-syntaksia käyttämällä:

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

2. Siirtymällä taulukkonäkymään sinun pitäisi nähdä seuraavat tulokset:

   Kaupunki	Suodatin
   Vaasa	Vaasa
   Kotka	Kotka
   Turku	Turku
   Tampere	Tampere
   Vaasa	Vaasa
   Turku	Turku
   Vaasa	Vaasa
   Turku	Turku
   Vaasa	Vaasa

3. Nämä tulokset eivät ehkä näytä kovin arvokkailta. Laajenna kuitenkin kyselyä ja muuta se yhdistämiskyselyksi käyttämällä seuraavaa SQL:ää:

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

4. Siirtymällä taulukkonäkymään sinun pitäisi nähdä seuraavat tulokset:

   Kaupunki	Suodatin
   <Kaikki>	*
   Kotka	Kotka
   Tampere	Tampere
   Turku	Turku
   Vaasa	Vaasa

   Access suorittaa yhdeksän edellä näytetyn tietueen yhdistämiskyselyn käyttämällä kiinteitä kenttäarvoja <Kaikki> ja ”*”.

   Koska tämä yhdistämiskyselylause ei sisällä avainsanaa UNION ALL, Access palauttaa vain erilliset tietueet, mikä tarkoittaa, että jokainen kaupunki palautetaan vain kerran kiinteine, samanlaisine arvoineen.

5. Nyt kun sinulla on valmis yhdistämiskysely, joka näyttää jokaisen kaupungin nimen vain kerran, sekä asetus, joka käytännössä valitsee kaikki kaupungit, voit käyttää tätä kyselyä tietuelähteenä lomakkeen yhdistelmäruudulle. Käyttämällä tätä erityisesimerkkiä mallina voit luoda lomakkeelle yhdistelmäruudun ohjausobjektin, määrittää tämän kyselyn sen tietuelähteeksi, määrittää Suodatin-sarakkeen sarakkeenleveysominaisuuden malliksi 0 (nolla) sen piilottamiseksi katseilta ja määrittää Sidottu sarake -ominaisuuden arvoksi 1 toisen sarakkeen indeksin. Lomakkeen oman suodatinominaisuuden kautta voit sitten lisätä esimerkiksi seuraavanlaisen koodin lomakkeen suodattimen aktivoimiseksi käyttämällä yhdistelmäruudun ohjausobjektissa valittua arvoa:

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

   Lomakkeen käyttäjä voi sitten suodattaa lomaketietueet, joissa on tietyn kaupungin nimi, tai valita <Kaikki> ja luetteloida kaikkien kaupunkien tietueet.

Sivun alkuun