Keskustelu: Ohjelmointikysymykset: MySQL haku taulujen liitos

Minulla on tekeillä ohjelma, johon lisätään pelaajien testituloksia. Tavoitteena olisi saada ulos kaikki syötetyt tulokset, jonka lisäksi halutaan kaikki rivit pelaajista ja testeistä.

Hakujen kannalta oleelliset taulut ovat:

• henkilot (perustiedot nimet, email, tiiviste yms.
• testit (testien nimet ja kuvaukset)
• yksikot (Testien mittaustulosten yksiköt m/s, s, kpl, m, kg yms)
• tulokset (testeistä mitatut tulokset numeerisena. Suorituksen päivämäärä viittaukset testitauluun ja henkilotauluun)

Rivimäärät tauluissa:

• henkilot 17
• tulokset 5
• testit 5
• yksikot 5

Tavoitteena on siis saada tulosjoukko, jossa on viimeisimmät tulokset testeistä pelaajittain. Tämä on varsin helppoa. Lisäksi tulosjoukkoon halutaan jokainen pelaaja jokaiseen testiin, jolloin sarakkeet 'tulos' ja 'paivamaara' ovat NULL.

Alla on tuloste taulusta tulokset.

id	testi_id	paivamaara		henkilo_id		tulos
1	1 [->]		2020-06-20		1 [->]			4.53
2	1 [->]		2020-06-20		2 [->]			4.28
3	2 [->]		2020-06-20		1 [->]			180.00
4	1 [->]		2020-06-30		1 [->]			4.16
5	1 [->]		2020-06-20		3 [->]			5.22

Haluttu lopputulos olisi muotoa:

etunimi	sukunimi	testi_id	paivamaara	tulos	yksikko
Joku	Jokunen		1			2020-06-18	4.22	s
Ville	Virtanen	1			2020-06-18	3.98	s
.
.
.
Keski	Ketterä		2			2020-06-18	NULL	kpl
.
.
.
Vika	Niminen		3			2020-06-18	190		s

Lopputuloksena pitäisi olla pelaajamäärä (17) * testimäärä (5), joista NULL poikkeava arvo pitäisi olla 5 rivillä. Yhteensä siis 85 riviä tulosjoukossa.

Sain aikaiseksi seuraavan SQL-kyselyn, jolla tulee toki oikeat syötetyt arvot, mutta tuloksena on vain viisi riviä.

SELECT
		henkilot.etunimi,
		henkilot.sukunimi,
		testit.nimi,
		tulokset.tulos,
		yksikot.yksikko
	FROM
		yksikot,
		tulokset
	LEFT JOIN
		testit ON testit.id = tulokset.testi_id
	LEFT JOIN
		henkilot ON tulokset.henkilo_id = henkilot.id
	WHERE
		yksikot.id = testit.yksikko_id

LEFT JOIN tarkoittaa, että vasemmalta tulevat kaikki rivit ja oikeanpuoleista ei ole pakko löytyä. Oikea liitos on siis juuri päinvastainen: SELECT ... FROM henkilot, testit LEFT JOIN tulokset ... LEFT JOIN yksikot.

Aivan oikein ajatus katkesi jossakin välissä pahasti.

Tulostus ei mene kuitenkaan vielä aivan täsmälleen oikein. Tuloksiin tulee id-numerolla tieto kenen suoritus kyseine rivi on. Nyt pitää siis saada vielä tämä yhteys paikalleen.

Alla olevalla kyselyllä tulee virhe, ettei taulu henkilot ole sopiva.

testisivu kirjoitti:

Column not found: 1054 Unknown column 'henkilot.id' in 'on clause''

SELECT
		henkilot.etunimi,
		henkilot.sukunimi,
		testit.nimi,
		tulokset.tulos,
		yksikot.yksikko
	FROM
		henkilot,
		testit
	LEFT JOIN
		tulokset ON testit.id = tulokset.testi_id AND henkilot.id = tulokset.henkilo_id
	LEFT JOIN
		yksikot ON yksikot.id = testit.yksikko_id

Seuraavalla kysely tietenkin onnistuu, mutta linkitys tuloksien ja henkilöiden väliltä puuttuu. Tuloksiin tulee siis suorituksia myös pelaajille, joilla ei ole vielä merkitty suoritusta kyseiseen testiin.

SELECT
		henkilot.etunimi,
		henkilot.sukunimi,
		testit.nimi,
		tulokset.tulos,
		yksikot.yksikko
	FROM
		henkilot,
		testit
	LEFT JOIN
		tulokset ON testit.id = tulokset.testi_id
	LEFT JOIN
		yksikot ON yksikot.id = testit.yksikko_id

Niin no tässähän olennainen kysymys on se, millä nimellä id on henkilötaulussa, kun kerran ei löydy. Eli ylempi kysely on aivan oikea, kunhan tarkastat taulustasi, mikä se henkilot.id kuuluisi olla.

henkilot taulussa on sarake nimeltä id, joten se ei ole ongelmana. Nähtävästi testit-taulu tulee liittää JOIN-avainsanalla mukaan. Ainakin nyt tulokset näyttävät tulevan juuri kuten pitääkin.

SELECT
	henkilot.id,
	henkilot.etunimi,
	henkilot.sukunimi,
	testit.nimi,
	tulokset.tulos,
	yksikot.yksikko
FROM
	henkilot
JOIN
	testit
LEFT JOIN
	tulokset ON testit.id = tulokset.testi_id AND tulokset.henkilo_id = henkilot.id
LEFT JOIN
	yksikot ON yksikot.id = testit.yksikko_id