En nyt keksinyt parempaakaan otsikkoa tälle. Tämän vastaus on varmaan ihan simppeli, mutta jostakin syystä ajatukset ei nyt vain kulje. Haluan siis saada taulusta 1 ulos kaikki ne rivit, joita ei löydy taulusta 2 ja joita ei ole poistettu.
Kokeilin seuraavilla parilla kyselyllä. Ensimmäinen toimii muuten paitsi silloin, kun taulu 2 on tyhjä. Toinen ei toiminut niinkään hyvin, sillä se antaa aina kaikki rivit taulusta 1, vaikka se löytyisikin taulusta 2 tai olisi poistettu.
SELECT taulu1.* FROM taulu1, taulu2 WHERE taulu1.id <> taulu2.taulu1_id AND taulu1.poistettu = 0 AND taulu2.poistettu = 0;
SELECT taulu1.* FROM taulu1 LEFT JOIN taulu2 ON taulu1.id <> taulu2.taulu1_id AND taulu1.poistettu = 0 AND taulu2.poistettu = 0;
Taulut ovat siis pelkistettyinä tämän tyyppisiä
taulu1
+----+--------+-----------+ | id | nimi | poistettu | +----+--------+-----------+ | 1 | nimi 1 | 0 | | 2 | nimi 2 | 1 | | 3 | nimi 3 | 0 | | 4 | nimi 4 | 0 | | 5 | nimi 5 | 0 | +----+--------+-----------+
taulu2
+----+-----------+-----------+ | id | taulu1_id | poistettu | +----+-----------+-----------+ | 1 | 1 | 0 | | 2 | 2 | 0 | | 3 | 3 | 1 | | 4 | 4 | 0 | | 5 | 4 | 1 | +----+-----------+-----------+
Tästä pitäisi siis tulla ulos seuraavaa:
+----+--------+-----------+ | id | nimi | poistettu | +----+--------+-----------+ | 3 | nimi 3 | 0 | | 5 | nimi 5 | 0 | +----+--------+-----------+
Ensimmäistä riviä ei tule, koska siihen löytyy viittaus taulusta 2. Toista ei tule, koska se on poistettu. Kolmas tulee, koska siihen osoittava viittaus on poistettu. Neljäs ei tule, koska taulusta 2 löytyy voimassaoleva viittaus siihen. Viides rivi tulee mukaan, koska siihen ei löydy viittausta taulusta 2.
Tuo LEFT JOINin käyttö näyttää olevan usein vaikeaa. Eihän sitä voi ollenkaan noin tehdä. Tuossa liitoksessa haetaan kaikki mahdolliset riviparit, joissa taulu1.id on eri kuin taulu2.taulu1_id. Saat esimerkiksi tulosrivin, jossa on taulu1:stä rivi id:llä 1 ja taulu2:sta rivi taulu1_id:llä 2. Tässä ei ole mitään järkeä; JOINissa pitää käytännössä aina olla =-merkki niiden sarakkeiden välillä, joiden mukaan taulut liittyvät toisiinsa, ja jos jotain mystisempiä ehtoja tarvitaan, ne kirjoitetaan erikseen.
Sinun pitää siis hakea LEFT JOINilla nimenomaan ne rivit, joilla taulu1_id = taulu1.id, ja WHERE-kohdassa pitää taas tarkistaa, että taulu1_id IS NULL eli että taulu2:sta ei löytynyt LEFT JOINiin sopivaa riviä.
SELECT taulu1.* FROM taulu1 LEFT JOIN taulu2 ON taulu1_id = taulu1.id AND taulu2.poistettu = 0 WHERE taulu1.poistettu = 0 AND taulu1_id IS NULL
En ole ihan varma, mitä tarkoitit kohdassa ”ja joita ei ole poistettu”. Yllä esimerkkini vastaa sitä, mitä sinun esimerkkitulosteessasi näkyy.
Miksei vain yksinkertaisesti IN-operaattorilla?
select * from taulu1 where id not in (select taulu1_id from taulu2 where poistettu=0) and poistettu=0
Vai ymmärsinkö jotain väärin?
Enpä minä ole koskaan kunnolla ymmärtänyt miten tuota JOINia käytetään. Oppaassa sanotaan, että "Siinä voi käyttää lisämerkintöjä LEFT ja RIGHT, jotka tarkoittavat, että vasemmasta tai oikeasta taulusta otetaan mukaan myös rivit, joilla ei ole vastinetta toisessa taulussa." En vain ymmärrä mikä on vasen ja mikä oikea taulu. Miten tuollainen vasen ja oikea määritellään?
Tuo Metabolixin antama kysely näyttäisi olevan juuri haetunlainen. Voiko, onko täysin turha tai jopa haitallista laittaa sen loppuun vielä "GROUP BY taulu1.id"?
Se "ja joita ei ole poistettu" kohta oli kyllä hiukan huonosti argumentoitu, mutta mielestäni viestin lopussa olevan esimerkin olisi pitänyt aukaista ongelmaa hyvin. Idea oli siis, että jos taulu1:n rivi on "poistettu", niin sitä ei valita ja jos taulu2:ssa oleva viite on "poistettu", viitettä ei huomioida.
En kyllä täysin ymmärrä tuon kyselyn logiikkaa. Miten taulu1_id IS NULL tarkoittaa, että taulu2:sta ei löytynyt LEFT JOINiin sopivaa riviä? Ja miten kyselystä saadaan ulos halutut taulu1:n rivit sillä, että taulu2:sta ei löydy LEFT JOINiin sopivaa riviä? Selkeyttäisi varmaan paljon, jos tietäisin millainen tulos tulee kahdesta JOINilla yhdistetystä taulusta ilman mitään ehtoja.
Eli tällainen (tai jos tuo ei ole oikeaoppinen kysely, niin jokin vastaava).
SELECT * FROM taulu1 LEFT JOIN taulu2
Olisiko sen tulos sitten tällainen?
+----+--------+-----------+------+-----------+-----------+ | id | nimi | poistettu | id | taulu1_id | poistettu | +----+--------+-----------+------+-----------+-----------+ | 1 | nimi 1 | 0 | 1 | 1 | 0 | | 1 | nimi 1 | 0 | 2 | 2 | 0 | | 1 | nimi 1 | 0 | 3 | 3 | 1 | | 1 | nimi 1 | 0 | 4 | 4 | 0 | | 1 | nimi 1 | 0 | 5 | 4 | 1 | | 1 | nimi 1 | 0 | NULL | NULL | NULL | | 2 | nimi 2 | 1 | 1 | 1 | 0 | | 2 | nimi 2 | 1 | 2 | 2 | 0 | | 2 | nimi 2 | 1 | 3 | 3 | 1 | | 2 | nimi 2 | 1 | 4 | 4 | 0 | | 2 | nimi 2 | 1 | 5 | 4 | 1 | | 2 | nimi 2 | 1 | NULL | NULL | NULL | | 3 | nimi 3 | 0 | 1 | 1 | 0 | | 3 | nimi 3 | 0 | 2 | 2 | 0 | | 3 | nimi 3 | 0 | 3 | 3 | 1 | | 3 | nimi 3 | 0 | 4 | 4 | 0 | | 3 | nimi 3 | 0 | 5 | 4 | 1 | | 3 | nimi 3 | 0 | NULL | NULL | NULL | | 4 | nimi 4 | 0 | 1 | 1 | 0 | | 4 | nimi 4 | 0 | 2 | 2 | 0 | | 4 | nimi 4 | 0 | 3 | 3 | 1 | | 4 | nimi 4 | 0 | 4 | 4 | 0 | | 4 | nimi 4 | 0 | 5 | 4 | 1 | | 4 | nimi 4 | 0 | NULL | NULL | NULL | | 5 | nimi 5 | 0 | 1 | 1 | 0 | | 5 | nimi 5 | 0 | 2 | 2 | 0 | | 5 | nimi 5 | 0 | 3 | 3 | 1 | | 5 | nimi 5 | 0 | 4 | 4 | 0 | | 5 | nimi 5 | 0 | 5 | 4 | 1 | | 5 | nimi 5 | 0 | NULL | NULL | NULL | +----+--------+-----------+------+-----------+-----------+
Nopealla vilkaisulla myös feelixin antama kysely näyttäisi halutunlaiselta. En kuitenkaan testannut sitä, koska tuo Metabolixin antama kysely on omassa koodissani helpommin toteutettavissa, joten käytän jokatapauksessa sitä.
AkeMake kirjoitti:
En vain ymmärrä mikä on vasen ja mikä oikea taulu. Miten tuollainen vasen ja oikea määritellään?
Vasen on se, joka on vasemmalla, ja oikea on se, joka on oikealla. Kai nyt aikuinen ihminen tietää, missä on vasen ja missä oikea.
SELECT * FROM vasen JOIN oikea
AkeMake kirjoitti:
Voiko, onko täysin turha tai jopa haitallista laittaa sen loppuun vielä "GROUP BY taulu1.id"?
Jos taulu1.id on uniikki (kuten PRIMARY KEY) ja jos taulu2 ei ikinä sisällä useaa poistamatonta riviä samalle taulu1_id:lle, GROUP BY on täysin turha. Muutenkin GROUP BY on järkevä lähinnä silloin, kun käytetään ryhmittelyä vaativia funktioita (COUNT, SUM, MAX jne.). Jos vain haluat erilaiset rivit, on minusta selvempää käyttää DISTINCT-sanaa.
AkeMake kirjoitti:
Miten taulu1_id IS NULL tarkoittaa, että taulu2:sta ei löytynyt LEFT JOINiin sopivaa riviä?
Kuten oppaassa selitetään, LEFT JOIN hakee vasemmanpuoleisen taulun (taulu1) rivit silloinkin, kun oikeanpuoleisessa taulussa (taulu2) ei ole ON-kohtaan sopivaa riviä. Silloin tulosrivillä kaikissa taulu2:n kentissä näyttää olevan NULL. Jos siis taulu2:sta löytyisi sopiva rivi, pitäisi päteä taulu2.taulu1_id = taulu1.id. Jos taas sellaista riviä ei löydy, pätee taulu2.taulu1_id IS NULL.
AkeMake kirjoitti:
Ja miten kyselystä saadaan ulos halutut taulu1:n rivit sillä, että taulu2:sta ei löydy LEFT JOINiin sopivaa riviä?
Ei pelkästään sillä saadakaan, vaan lisäksi pitää tarkistaa, että riviä ei ole poistettu.
AkeMake kirjoitti:
Selkeyttäisi varmaan paljon, jos tietäisin millainen tulos tulee kahdesta JOINilla yhdistetystä taulusta ilman mitään ehtoja.
Tee pienet taulut ja kokeile.
AkeMake kirjoitti:
Olisiko sen tulos sitten tällainen? – –
Lähes, mutta taulu2:sta ei tule NULL-arvoja, jos jokin sopiva rivi löytyy. NULL-arvot tulevat vain silloin, kun mikään muu rivi ei sovi ON-ehtoihin.
Sehän selkeytti kummasti. Olin koettanut aikaisemmin näitä vasenta ja oikeaa miettiä liian monimutkaisesti sen tietokannan näkökulmasta ja se tuntui jotenkin typerältä varsinkin kun phpMyAdmin näyttää taulut allekkain. En tajunnut, että se tarkoittaa yksinkertaisesti järjestystä kyselyssä.
Ja nuo loputkin vastaukset avasivat hyvin ymmärryksen solmukohtia. Kiitos.
Aihe on jo aika vanha, joten et voi enää vastata siihen.