Korišćenje upita za združivanje radi kombinovanja više upita u jedan rezultat

U primeru iz prethodnog odeljka u kojem se koristi Northwind baza podataka kombinuju se samo podaci iz dve tabele. Međutim, veoma lako možete da iskombinujete tri ili više tabela u upit za združivanje. Na primer, na osnovu prethodnog primera, u rezultat upita možete da uključite i imena zaposlenih. Možete da postignete taj zadatak dodavanjem trećeg upita i kombinovanjem sa prethodnim SQL izrazom uz dodatnu ključnu reč UNION, na sledeći način:

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

Kada pogledate rezultate u prikazu lista sa podacima, svi zaposleni će biti navedeno ime preduzeća uzorak, koji verovatno nije korisno. Ako želite da to polje pokazuje da li je osoba interni zaposleni, zaposleni dobavljača ili zaposleni klijenta, možete da navedete fiksnu vrednost umesto imena preduzeća. Evo kako bi SQL izgledao:

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

Evo kako izgleda rezultat u prikazu lista sa podacima. Access prikazuje ovih pet probnih zapisa:

Upit iznad može se još više smanjiti jer Access čita samo imena izlaznih polja iz prvog upita u upitu za združivanje. Ovde vidite da smo uklonili izlaz iz odeljaka drugog i trećeg upita:

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

U Access upitu za združivanje naručivanje je dozvoljeno samo jednom, ali svaki upit može da se filtrira pojedinačno. Na osnovu upita za združivanje u prethodnom odeljku, evo primera toga gde smo filtrirali svaki upit dodavanjem odredbe 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];

Prebacite se na prikaz lista sa podacima i videćete rezultate slične ovima:

Ako su upiti za združivanje veoma različiti, možete naići na situaciju u kojoj se u izlaznom polju moraju kombinovati podaci različitih tipova. U tom slučaju, upit za združivanje će najčešće davati rezultate kao podatke tekstualnog tipa jer taj tip podataka može da sadrži i tekst i brojeve.

Da biste saznali kako ovo funkcioniše, koristićemo upit za združivanje Transakcije proizvoda u Northwind bazi podataka. Otvorite tu probnu bazu podataka, a zatim otvorite upit Transakcije proizvoda u prikazu lista sa podacima. Poslednjih deset zapisa trebalo bi da budu slični ovom izlazu:

Recimo da želite da polje „Količina“ podelite na dva – „Kupovina“ i „Prodaja“. Recimo da želite da imate na fiksnu vrednost nula za polje bez vrednosti. Evo kako izgleda SQL za ovaj upit za združivanje:

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; 

Ako se prebacite na prikaz lista sa podacima, videćete poslednjih deset zapisa koji su sada prikazani ovako:

Nastavljajući sa ovim primerom, šta ako želite da polja sa nulom budu prazna? Možete da izmenite SQL tako da ne prikazuje ništa umesto nule ako dodate ključnu reč „Null“ na sledeći način:

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;

Međutim, kao što ste možda primetili prilikom prebacivanja na prikaz lista sa podacima, sada imate neočekivani rezultat. U koloni „Kupovina“ je opozvan izbor u svakom polju:

To se događa zato Access određuje tipove podataka polja iz prvog upita. U ovom primeru „Null“ nije broj.

Šta se dešava ako pokušate da umetnete praznu nisku umesto prazne vrednosti polja? SQL u ovom pokušaju može da izgleda ovako:

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;

Kada se prebacite na prikaz lista sa podacima, videćete da Access preuzima vrednosti stavke „Kupovina“, ali ih je konvertovao u tekst. Znate da su ovo tekstualne vrednosti po tome što su poravnate ulevo u prikazu lista sa podacima. Prazna niska u prvom upitu nije broj, zbog čega i vidite ove rezultate. Primetićete da su i vrednosti stavke „Prodaja“ konvertovane u tekst jer zapisi o kupovini sadrže praznu nisku.

Kako da rešite ovu zagonetku?

Rešenje je da nametnete da upit očekuje da vrednost polja bude broj. To se može postići pomoću izraza:

IIf(False, 0, Null)

Uslov koji treba proveriti, False, nikada neće biti True, tako da će, samim tim, izraz uvek vraćati Null, ali Access i dalje procenjuje obe opcije izlaza i odlučuje da izlaz bude numerički ili „Null“.

Evo kako možemo da koristimo ovaj izraz u ovom radnom primeru:

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;

Imajte na umu da nije potrebno menjati drugi upit.

Ako se prebacite na prikaz lista sa podacima, videćete rezultat koji želimo:

Drugi način da postignete isti rezultat jeste da dodate upite ispred upita za združivanje sa nekim drugim upitom:

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

Za svako polje Access vraća fiksne vrednosti tipa podataka koji definišete. Naravno, ne želite da se izlaz ovog upita meša sa rezultatima, a da biste to izbegli, treba da dodate odredbu WHERE vrednosti False:

WHERE False

To je mali trik jer ovo uvek ima vrednost „false“, pa upit ne vraća ništa. Kada iskombinujemo ovu izjavu sa postojećim SQL-om, dobijamo dovršenu izjavu koja glasi ovako:

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;

Poseban slučaj upita za združivanje jeste kombinovanje skupa zapisa sa jednim zapisom koji sadrži zbir jednog ili više polja.

Evo još jednog primera koji možete da napravite u probnoj Northwind bazi podataka da bismo ilustrovali kako da dobijete ukupnu vrednost u upitu za združivanje.

1. Napravite novi jednostavan upit da biste prikazali kupovinu piva (ID proizvoda = 34 u Northwind bazi podataka) koristeći sledeću SQL sintaksu:

   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. Pređite na prikaz lista sa podacima i trebalo bi da vidite četiri kupovine:

   Datum prijema	Količina
   22.1.2006.	100
   22.1.2006.	60
   4.4.2006.	50
   5.4.2006.	300

3. Da biste dobili ukupnu vrednost, napravite jednostavan agregatni upit pomoću sledećeg SQL-a:

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

4. Pređite na prikaz lista sa podacima i trebalo bi da vidite samo jedan zapis:

   Maksimalan datum prijema	Zbir količine
   5.4.2006.	510

5. Iskombinujte ova dva upita u upit za združivanje da biste na početak zapisa o kupovini dodali zapis sa ukupnom količinom:

   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. Prebacite se na prikaz lista sa podacima i trebalo bi da vidite četiri kupovine sa zbirom za svaku od njih, nakon čega dolazi zapis sa ukupnom količinom:

   Datum prijema	Količina
   22.1.2006.	60
   22.1.2006.	100
   4.4.2006.	50
   5.4.2006.	300
   5.4.2006.	510

To bi bilo sve o osnovama dodavanja ukupnih vrednosti u upit za združivanje. Ne bi bilo loše ni da dodate fiksne vrednosti u oba upita, kao što su „Detalj“ i „Ukupna vrednost“ da biste vizuelno razdvojili zapis sa ukupnom vrednošću od ostalih zapisa. Možete da pregledate pomoću fiksnih vrednosti u odeljku Kombinujte tri ili više tabela ili upita u upit za združivanje.

Upiti za združivanje u programu Access podrazumevano obuhvataju samo jedinstvene zapise. Ali, šta ako želite da uključite sve zapise? Drugi primer bi ovde bio koristan.

U prethodnom odeljku, pokazali smo vam kako da napravite ukupnu vrednost u upitu za združivanje. Izmenite SQL tog upita za združivanje da biste obuhvatili ID proizvoda = 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];

Pređite na prikaz lista sa podacima i trebalo bi da vidite prilično nepouzdan rezultat:

Jedan zapis, naravno, ne vraća dvaput veću ukupnu količinu.

Ovaj rezultat vidite zato što je jednog dana ista količina čokolade prodata dva puta – kao što je evidentirano u tabeli „Detalji o porudžbenici“. Evo jednostavnog upita za izdvajanje koji prikazuje oba zapisa u Northwind bazi podataka:

U pomenutom upitu za združivanje možete da vidite da polje „ID porudžbenice“ nije obuhvaćeno i da dva polja ne čine dva jedinstvena zapisa.

Ako želite da obuhvatite sve zapise, koristite UNION ALL umesto UNION u SQL-u. To će verovatno uticati na sortiranje rezultata, tako da ne bi bilo loše da dodate odredbu ORDER BY da biste odredili redosled sortiranja. Evo izmenjenog SQL-a na osnovu prethodnog primera:

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

Prebacite se na prikaz lista sa podacima i trebalo bi da kao poslednji zapis vidite sve detalje pored ukupne vrednosti:

Uobičajena upotreba upita za združivanje jeste da se on koristi kao izvor zapisa za kontrolu kombinovanog okvira u obrascu. Možete da koristite taj kombinovani okvir da biste izabrali vrednost za filtriranje zapisa u obrascu. Na primer, za filtriranje podataka o zaposlenima prema gradu.

Da biste videli kako ovo funkcioniše, evo još jednog primera koji možete da napravite u probnoj Northwind bazi podataka radi ilustracije ovog scenarija.

1. Napravite jednostavan upit za izdvajanje pomoću sledeće SQL sintakse:

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

2. Pređite na prikaz lista sa podacima i trebalo bi da vidite sledeće rezultate:

   Grad	Filter
   Subotica	Subotica
   Novi Sad	Novi Sad
   Kragujevac	Kragujevac
   Sombor	Sombor
   Subotica	Subotica
   Kragujevac	Kragujevac
   Subotica	Subotica
   Kragujevac	Kragujevac
   Subotica	Subotica

3. Gledajući te rezultate, možda nećete uspeti da vidite u čemu je njihova vrednost. Međutim, razvijte upit i transformišite ga u upit za združivanje koristeći sledeći SQL:

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

4. Pređite na prikaz lista sa podacima i trebalo bi da vidite sledeće rezultate:

   Grad	Filter
   <Sve>	*
   Novi Sad	Novi Sad
   Sombor	Sombor
   Kragujevac	Kragujevac
   Subotica	Subotica

   Access objedinjuje devet prethodno prikazanih zapisa sa fiksnim vrednostima polja <Sve> i „*“.

   Pošto ova odredba za združivanje ne sadrži UNION ALL, Access vraća samo jedinstvene zapise, što znači da se svaki grad vraća samo jednom sa fiksnim identičnim vrednostima.

5. Pošto sada imate dovršeni upit za združivanje koji prikazuje ime svakog grada samo jednom, kao i opciju koja efikasno bira sve gradove, možete da koristite ovaj upit kao izvor zapisa za kombinovani okvir u obrascu. Koristeći ovaj konkretni primer kao model, možete da napravite kontrolu kombinovanog okvira u obrascu, postavite ovaj upit kao izvor zapisa, postavite svojstvo „Širina kolone“ za kolonu „Filter“ na 0 (nula) da biste ga vizuelno sakrili i da zatim postavite svojstvo „Povezana kolona“ na vrednost 1 da biste označili indeks druge kolone. U svojstvu „Filter“ u samom obrascu zatim možete da dodate kôd sličan dolenavedenom da biste aktivirali filter obrasca pomoću vrednosti stavke koja je izabrana u kontroli kombinovanog okvira:

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

   Korisnik obrasca može zatim da filtrira zapise obrasca po imenu određenog grada ili da izabere <Sve> da bi prikazao listu svih zapisa za sve gradove.

Vrh stranice