Keskustelu: Nettisivujen teko: MySql4 ja alikyselyt ongelma

Elikästä, minulla on tietoja tälläisessä muodossa:

Taulu1:
id        | muita tietoja jne.
------------------------------
11        | jotain
5         | jotain
14        | jotain

Taulu2:
userid    | tunniste
---------------
11        | string1
11        | string2
11        | string3
14        | string4
5         | string5
5         | string6

Taulu3:
userid    | tunniste
---------------
11        | string7
11        | string8
5         | string9
5         | string10

Ja haluaisin hakea kaikki tiedot taulu1:sestä, ja lisäksi tiedot siitä montako tunnistetta on sekä taulu2:dessa että taulu3:ssa. Eli että se palauttaisi näin:

userid    | muita tietoja   | taulu2count   | taulu3count
---------------------------------------------------------
11        | jotain          | 3             | 2
5         | jotain          | 2             | 2
14        | jotain          | 1             | 0

Nyt kysely menee näin, mutta eihän se tieten toimi:

SELECT * FROM
taulu1,
(SELECT userid, COUNT(tunniste) as taulu2määrä FROM taulu2 GROUP BY userid) AS taulu2,
(SELECT userid, COUNT(tunniste) as taulu3määrä FROM taulu3 GROUP BY userid) AS taulu3
WHERE taulu2.userid = taulu1.id AND taulu3.userid = taulu1.id

Se toimii jotenkuten, mutta jos taulusta 2 tai 3 ei löydy vastaavaa userid:tä niin se ei tulosta koko rivin tietoja laisinkaan...

Vähän taas työläämpi kysymys, mutta en haluakaan mitään valmista kyselyä (vaikka tieten sekin käy ;) vaan jos joku MySql-velho osaisi vähän opastaa että missä on menty metsään niin se olisi suuri apu.

Eikö todellakaan noihin alikyselyihin saa mitenkään tuon pääkyselyn tietoja välitettyä, siis että se olisi jotenkin näin (pseudoa):

SELECT * FROM
taulu1,
(SELECT userid, COUNT(tunniste) as taulu2määrä FROM taulu2 WHERE userid = taulu1.id GROUP BY userid) AS taulu2

Jotenkin tuntuu etten ole päässyt lopulta selville tästä SQL:n syvimmästä olemuksesta, tai siis näiden kyselyiden logiikasta. Kirja aiheesta on kyllä lainaus/ostolistan kärjessä...

Tämä nyt ei ehkä ole tietokantagurun mielestä se oikea tapa toteuttaa tuota, mutta ite tekisin tuon niin, että lisäisin noihin 2- ja 3-tauluihin kentän visible (tinyint(1) not null default 1), jonka jälkeen tuon esimerkkituloksesi voisi saada suht yksinkertaisella joinilla:

SELECT
   t1.id
   SUM(t2.visible) AS taulu2maara,
   SUM(t3.visible) AS taulu3maara
FROM
   taulu1 AS t1
LEFT JOIN
   taulu2 AS t2 ON t1.id = t2.userid
LEFT JOIN
   taulu3 AS t3 ON t1.id = t3.userid
GROUP BY
   t1.id

Jos tulee tarvetta poistaa tietoja, niin voi suunnitella sovelluksen niin, että poistaminen on yhtä kuin merkata visible nollaksi, Näin ollen webbisoftan tietoturvakin paranee hieman, kun ei tarvitse ajaa DELETE-lauseita softasta.

Tosin tästä voi tosiaan olla joku oikea tietokantasuunnittelija olla hieman eri mieltä, mutta näin olen itse kokemuksen kautta oppinut tekemään.

Toinen vaihtoehto on ajaa kaksi kyselyä samalla JOIN-periaatteella, mutta siinäkin tulee kyllä ongelmansa vastaan, jos haluat kuitenkin hakea taulu1:stä kaikki rivit ja parittaa ne esim taulu2:sen kanssa, jolloin oikea JOIN on nimenomaan tuo LEFT JOIN, niin jos taulu2:sta ei löydy paria, niin sen arvo on NULL, mutta COUNT(*) kuitenkin tässä tapauksessa palauttaa 1... Toivottavasti nyt ei menny liian sekavaksi :)

Left Joinia kokeilin tuohon tapaan, tosin siinä käytin COUNTia enkä SUMia ja jostain minulle tuntemattomasta syystä se ei toiminut oikein.

Epäilemättä tuo antamasi malli toimii halutulla tavalla, mutta se ei kyllä vaikuta hirveän optimaaliselta toteutustavalta, pitäisi tehdä yksi kenttä käytännössä ihan turhaan. Eli mieluummin pitäytyisin nykyisissä taulurakenteissa, sillä en jaksa uskoa etten silläkin haluttua toimintoa saisi toimimaan, ja vieläpä nätisti ja yksinkertaisesti :) Mutta kiitos ratkaisuehdotuksesta.

Joo, itseasiassa testasin tuollaista kyselyä ja eihän se edes toimi oikein tuolla lailla kolmesta taulusta dataa haettaessa :)

Mutta kyllä tuo

SELECT
   t1.id
   COUNT(*) AS taulu2maara,
FROM
   taulu1 AS t1
LEFT JOIN
   taulu2 AS t2 ON t1.id = t2.userid
GROUP BY
   t1.id

pitäisi toimia, tosin se palauttaa aina taulu2määräksi vähintään ykkösen.

Mutta tosiaan jos löydät tuohon jonkun optimaalisen tavan, niin kerro toki. Minä en ole löytänyt, tosin en ole alikyselyitä MySQL:ssä juurikaan käyttänyt, sillä ne toimivat vielä vähän niin ja näin, kun weppihotellit eivät jaksa päivitellä softiaan.

Edit: No, pikku perehtyminen alikyselyihin tuotti tulosta. Itellä ainakin toimi seuraava (MySQL 5):

 SELECT
   u.id,
   (SELECT COUNT(*) FROM SIPA_g_comments WHERE uid = u.id) AS kommentteja,
   (SELECT COUNT(*) FROM SIPA_f_messages WHERE uid = u.id) AS viesteja
FROM
   SIPA_users AS u

Kaiken järjen mukaan pitäisi toimia myös MySQL 4.1 =>

Eipä mennyt tämäkään päivä hukkaan :)

Edit2: hups, puolet kyselystä puuttui.

ajv kirjoitti:

Edit: No, pikku perehtyminen alikyselyihin tuotti tulosta. Itellä ainakin toimi seuraava (MySQL 5):

 SELECT
   u.id,
   (SELECT COUNT(*) FROM SIPA_g_comments WHERE uid = u.id) AS kommentteja,
   (SELECT COUNT(*) FROM SIPA_f_messages WHERE uid = u.id) AS viesteja

Kaiken järjen mukaan pitäisi toimia myös MySQL 4.1 =>

Eipä mennyt tämäkään päivä hukkaan :)

Ei taida valitettavasti toimia. Valittaa tuntemattomasta taulusta. Pitääpä tarkistaa tukeeko hostingini MySQL5:sta, muistaakseni kyllä ei..

Jahas, siitä puuttuikin oleellinen osa, jo mietinkin että aika ihmeellisen näköinen kysely on.. Kokeillaanpas nyt tuota kun löysin järjenkin tuosta ;)

Edit:
Katsos vain, sehän toimii! Ja vieläpä ymmärrän taian kuinka se tehtiin, ei paremmin homma enään olisi voinut mennä (paitsi tietenkin jos olisin itse älynnyt tunkea sen alikyselyn tuohon väliin ;). Suuri kiitos ajv.

Siis tukeehan se MySQL-versiosi ylipäätänsä noita alikyselyitä? Kuten tiedetään, ne eivät ole ollut mikään itsestäänselvyys puhuttaessa MySQL:stä, vaan pikemminkin aika uusi juttu. Tuki tuli muistaakseni ekan kerran versioon 4.1.jotain.

Edit:
http://dev.mysql.com/doc/refman/4.1/en/subqueries.html
"Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported..."

Edit2: Aha, saitkin toimimaan, hyvä! Ei kestä kiittää, opinpahan itekkin tuossa hieman uutta!

ajv kirjoitti:

Siis tukeehan se MySQL-versiosi ylipäätänsä noita alikyselyitä? Kuten tiedetään, ne eivät ole ollut mikään itsestäänselvyys puhuttaessa MySQL:stä, vaan pikemminkin aika uusi juttu. Tuki tuli muistaakseni ekan kerran versioon 4.1.jotain.

Edit:
http://dev.mysql.com/doc/refman/4.1/en/subqueries.html
"Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported..."

Juu, päivällä jo tuon päivityksen kanssa taistelin kun aiempi versioni ei alikyselyitä tukenut. Mutta kuten jo tuohon aiempaan muokkaukseen pistin niin sain sen nyt toimimaan.