Jednostavan spoj dve tabele (A simple join of two tables)

Nastavak primera iz prethodnog posta.

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. Ubacite sledeci kod

select porudzbine.brojporudzbine, porudzbine.iznos, porudzbine.date
from kupci, porudzbine
where kupci.ime = 'Nikola Nikolic'
and kupci.sifrakupca = porudzbine.sifrakupca;

4, Klik na dugme "Go".

Za odgovor na ovaj upit potrebni su nam podaci iz obe tabele, morali smo da navedemo obe.
Kada zadate dve tabele u upituzadajete i nacin njihovog spanja. 

Da bi videli porudzbine odredjenog kupca povezane s tim kupcewm i samo s njim, koristimo odredbu WHERE pomocu koje zdajemo uslov spajanja sto je poseban tip uslovnog izraza. U njemu treba da navedete atribute koji povezuju  dve tabele. 

U nasem primeru, uslov spajanja je:

kupci.sifrakupca = porudzbine.sifrakupca

Ovaj uslov MySQL-u govori da u tabelu rezultata upita izdvoji samo redove u kojima su vrednosti u koloni sufrakupca iz tabele kupci jedanake vrednostima u koloni sifrakupca iz tabele porudzbine.

Posto ste upitu dodali taj uslov, promenili ste tip spoja i dobili ste jednakovredan spoj (en. equi-join). 

Notacija s tackom ozmacava iz koje tabele potice kolona. 

Tako kupci.sifrakupca znaci da je u pitanju kolona sifrakupca iz tabele kupci. A kod porudzbine.sifrakupca znaci da je u pitanju kolona sifrakupca iz tabele porudzbine.

Upotreba notacije s tackom je obavezna ako istoimena kolona postoji u vise tabela.  Notacija sa tackom moze se upotrebiti i za nedvosmisleno identifikovanje kolona iz razlicitih baza podataka. 

Primer: 

motori.porudzbine.sifrakupca - nekadrugabaza.porudzbine.sifrakupca

Upotreba punih oznaka kolona nije obavezna ali olaksava citanje i odrzavanje upita.

kupci.ime = 'Nikola Nikolic'

Prethodni post - Ucitavanje podataka iz vise tabela istovremeno.
Sledeci post - Spajanje vise od dve tabele.

Continuation of examples from the previous post.

1. Start XAMPP. Click on the Admin in line of MySQL or type in the address bar http://localhost/phpmyadmin/
2. From the left select the database lovemotobikes.
3. Insert the following code

SELECT orders.orderid, orders.amount, orders.date
FROM customers, orders
WHERE customers.name = 'Nikola Nikolic'
and customers.customerid = orders.customerid;

4, Click on the "Go"button.

In response to this query, we need data from both tables, we had to name two.
When given two different tables in a query and you have to specify the way of their joinig.

To view a specific customer orders associated with that customer and only with him, use WHERE clause,  which we give a condition for joining which is a special type of conditional expression. It should specify the attributes that joins the two tables.

In our case, the condition of the joining is:

customers.customerid = orders.customerid

This condition says to MySQL that in the table of query results set aside only the rows where the values ​​in the column customerid of table customers are the same values as ​​in a column customerid in the table orders.

After you added a query that condition, you changed the type of joinig and got you equalvalue - equi-join).

The notation with a dot idicates from what table is column.

So customers.customerid means that it is column customerid from a table customers. And orders.customerid means that it is column customerid from a table orders.

Using the dot notation is required if there is a column of the same name exists in several tables. Dot notation can be used for unambiguous identification of the column from different databases.

Example:

motobikes.orders.customerid - someotherdb.orders.customerid

Using full label column is not required but makes it easier to read and maintain queries.

customers.name = 'Nikola Nikolic'

Previous post - Loading data from multiple tables at the same time.

Next post - Joining more than two tables.