Website Development Prices

Website Development Prices

Thursday, May 21, 2015

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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.