Luku 3

Liitostaulu

Taulujen välillä esiintyy yleensä kahdenlaisia suhteita:

  1. Yksi moneen -suhde: Taulun A rivi liittyy enintään yhteen taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.
  2. Monta moneen -suhde: Taulun A rivi voi liittyä useaan taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.

Tapauksessa 1 voimme lisätä tauluun A sarakkeen, joka viittaa tauluun B, kuten teimme edellisen aliluvun esimerkissä. Tapauksessa 2 tilanne on kuitenkin hankalampi, koska yksittäinen viittaus kummankaan taulun rivissä ei riittäisi. Ratkaisuna on luoda kolmas liitostaulu, joka sisältää tiedot viittauksista.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa verkkokaupassa on tuotteita ja asiakkaita ja jokainen asiakas on valinnut tiettyjä tuotteita ostoskoriin. Tietyn asiakkaan korissa voi olla useita tuotteita, ja toisaalta tietty tuote voi olla usean asiakkaan korissa.

Rakennamme tietokannan niin, että siinä on kolme taulua: Tuotteet, Asiakkaat ja Ostokset. Liitostaulu Ostokset ilmaisee, mitä tuotteita on kunkin asiakkaan ostoskorissa. Sen jokainen rivi esittää yhden parin muotoa "asiakkaan x korissa on tuote y".

Oletamme, että taulujen sisällöt ovat seuraavat:

Nyt voimme hakea asiakkaat ja tuotteet seuraavasti:

SELECT A.nimi, T.nimi FROM Asiakkaat A, Tuotteet T, Ostokset O
WHERE A.id = O.asiakas_id AND T.id = O.tuote_id;

Kyselyn ideana on hakea tauluista Asiakkaat ja Tuotteet taulun Ostokset rivejä vastaavat tiedot. Jotta saamme mielekkäitä tuloksia, kytkemme rivit yhteen kahden ehdon avulla. Kysely tuottaa seuraavan tulostaulun:

nimi        nimi      
----------  ----------
Uolevi      porkkana  
Uolevi      selleri   
Maija       retiisi   
Maija       lanttu    
Maija       selleri   

Miten kysely toimii?

Voimme taas tutkia kyselyn toimintaa hakemalla kaikki sarakkeet ja poistamalla ehdot:

SELECT * FROM Asiakkaat A, Tuotteet T, Ostokset O;

Tämän kyselyn tulostaulussa on kaikki tavat valita jokin asiakas, tuote ja ostokset. Tulostaulussa on 5 * 3 * 5 = 75 riviä ja se alkaa näin:

id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      1           retiisi     7           1           2         
1           Uolevi      1           retiisi     7           1           5         
1           Uolevi      1           retiisi     7           2           1         
1           Uolevi      1           retiisi     7           2           4         
1           Uolevi      1           retiisi     7           2           5         
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      2           porkkana    5           1           5         
1           Uolevi      2           porkkana    5           2           1         
1           Uolevi      2           porkkana    5           2           4         
1           Uolevi      2           porkkana    5           2           5         
1           Uolevi      3           nauris      4           1           2         
1           Uolevi      3           nauris      4           1           5         
1           Uolevi      3           nauris      4           2           1         
1           Uolevi      3           nauris      4           2           4         
1           Uolevi      3           nauris      4           2           5         
...

Sitten kun lisäämme kyselyyn ehdot, saamme rajattua kiinnostavat rivit:

SELECT * FROM Asiakkaat A, Tuotteet T, Ostokset O
WHERE A.id = O.asiakas_id AND T.id = O.tuote_id;
id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         
2           Maija       1           retiisi     7           2           1         
2           Maija       4           lanttu      8           2           4         
2           Maija       5           selleri     4           2           5     

Kun vielä määritämme halutut sarakkeet, tuloksena on lopullinen kysely:

SELECT A.nimi, T.nimi FROM Asiakkaat A, Tuotteet T, Ostokset O
WHERE A.id = O.asiakas_id AND T.id = O.tuote_id;
nimi        nimi      
----------  ----------
Uolevi      porkkana  
Uolevi      selleri   
Maija       retiisi   
Maija       lanttu    
Maija       selleri   

Lisää ehtoja kyselyyn

Voimme lisätä kyselyyn lisää ehtoja, jos haluamme saada selville muuta ostoskoreista. Esimerkiksi seuraava kysely hakee Maijan korissa olevat tuotteet:

SELECT T.nimi
FROM Asiakkaat A, Tuotteet T, Ostokset O
WHERE A.id = O.asiakas_id AND T.id = O.tuote_id AND A.nimi = 'Maija';
nimi      
----------
retiisi   
lanttu    
selleri   

Seuraava kysely puolestaan kertoo, keiden korissa on selleri:

SELECT A.nimi
FROM Asiakkaat A, Tuotteet T, Ostokset O
WHERE A.id = O.asiakas_id AND T.id = O.tuote_id AND T.nimi = 'selleri';
nimi      
----------
Uolevi    
Maija    
Jatka tästä seuraavaan osaan: