Spajanje vise od dve tabele (Joinig more than two tables)

Pravilo glasi da se tabele spajaju  u parovima pomocu uslova za spajanje. 

Primer: ako zelite da znate koji su kupci kupili motore odredjenog tipa (zato sto mozda zelite da im posaljete obavestenja o tipu motora), potrebno je da analizirate odnose izmedju vise tabela.

Treba da nadjete kupce s najmanje jednom porudzbinom koja u tabeli stvari_porudzbina sadrzi ime motora Suzuki. Da biste od tabele kupci stigli do tabele porudzbine, upotrebite indentifikator sifrakupca. Da biste od tabele porudzbine stigli do tabele stvari_porudzbina, upotrebite indentifikator brojporudzbine. Da biste od tabele stvari_porudzbina stigli do odredjenog imena motora u tabeli motori, upotrebite brojmotora.

Posto uspostavite sve te veze, ispitajte da li se u imenima motora, nalazi rec Suzuki. i ispisite imena kupaca koji su kupili motore s tom recju u imenu motora.

To radi sledeci upit (ovaj sacuvajte kao spajanje-vise-do-dve-tabele.sql):
1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore.

3. Izaberite karticu SQL i ubacite sledeci kod

select kupci.ime 
from kupci, porudzbine, stvari_porudzbina, motori
where kupci.sifrakupca = porudzbine.sifrakupca
and porudzbine.brojporudzbine = stvari_porudzbina.brojporudzbine
and stvari_porudzbina.brojmotora = motori.brojmotora

and motori.imemotora like '%Suzuki%';

4. Klik na dugme "Go".

Ovaj upit daje sledeci rezultat niko nije kupio Suzuki motor.

ili ubacite ovaj kod

select kupci.ime 
from kupci, porudzbine, stvari_porudzbina, motori
where kupci.sifrakupca = porudzbine.sifrakupca
and porudzbine.brojporudzbine = stvari_porudzbina.brojporudzbine
and stvari_porudzbina.brojmotora = motori.brojmotora

and motori.imemotora like '%KTM%';

Ovaj upit daje sledeci rezultat.

Povezali smo podatke iz 4 razlicite tabele, da bismo napravili jednakovredne spojeve, bili su nam potrebna tri razlicita uslova spajanja. 

Za spajanje jednog para tabele najcesce je potreban jedan uslov, ukupan broj uslova za je za jedan manji od kupnog broja tabela.


The rule is that the tables are combined into pairs using the conditions for joining.

Example: if you want to know which customers bought certain types of motobikes (because you might want to send them info about the type of motobike), it is necessary to analyze the relationships between multiple tables.

You need to find customers with at least one item in the table order_items that contains the  motobike_name Suzuki. To get from table customers to the table orders, use the identifier customerid. To get from table orders to the table order_items, use the identifier ordersid. To get from table order_items to certain names in the table motobikes, use motobikeid.

When you establish all these connections, check whether in the names of the motobikes, is word Suzuki. and print the names of customers who bought the motobikes with the word in the name of the motobike.

The following query works that (this save as joinig-more-than-two-tabels.sql):
1. Start XAMPP. Click on the Admin in line MySQL or type in the address bar http://localhost/phpmyadmin/

2. From the left select the database lovemotobikes.
3. Select the SQL tab and insert the following code

SELECT customers.name
FROM customers, orders, order_items, motobikes
WHERE customers.customersid = orders.customersid
AND orders.ordersid = order_items.ordersid
AND order_items.motobikeid = motobikes.motobikeid

AND motobikes.motobike_name like '%Suzuki%';

4. Click on the "Go".

This inquiry gives the following result nobody bought Suzuki motobikes.

or insert this code

SELECT customers.name
FROM customers, orders, order_items, motobikes
WHERE customers.customersid = orders.customersid
AND orders.ordersid = order_items.ordersid
AND order_items.motobikeid = motobikes.motobikeid

AND motobikes.motobike_name like '%KTM%';

This inquiry gives the following result.

We connected the data from 4 different tables, in order to make an equivalent joins, we needed three different joining conditions.


To connect one pair of table is most often required a  onecondition, the total number of conditions is less for one than the number for of the all tables.