RATKAISTU: SQL kysely - taulujen yhdistäminen SUBSTRING_INDEX-funktion arvolla

[JA5U]

Moi

Tarttis saada taulukoitua dataa eräästä verkkokyselystä, johon liittyy kaksi keskeistä tietokantataulua.

Data on tallennettu tietokantaan siten, että vastaustaulussa on sarakkeet: lomake_id, vastaus_id, kenttä_id ja vastaus_data.
So. yhdestä vastauksesta muodostuu aina lomakekenttien määrää vastaava määrä rivejä.

Nyt haluaisin ensinnäkin lomakekentän ID 85 vastaus_data-sarakkeen arvon.
Lisäksi tarvitsen kentän ID:89, jonka arvo noudattaa aina muotoa: id_123, ja josta pitäisi SUBSTRING_INDEX-funktiolla tai vast. saada tuo numero-osa esim. 123.
Tuolla numerolla pitäisi dataan yhdistää vielä toisen vastausvaihtoehdot-taulun NAME-sarake.

Lopputulokset pitäisi olla siis jotakuinkin:

Koodia: [Valitse]

vastaus_id, kenttä_id_85_data, name
1, "Irti muumeista", "Vaihtoehdon nimi"
Pystyykö kukaan näiden tietojen valossa auttamaan?

[retu]

Tuosta: http://mikehillyer.com/articles/an-introduction-to-database-normalization/ ja helpottaa oloa jatkossa

Mutta muuten...

Olettaen että on kyse mysql kannasta.
Olettaen että taulujen nimet ovat vastaus ja vastausvaihtoehdot.
Olettaen että vastaus taulun lomake_id on numeerinen ja kenttä_id varchar tms.
Olettaen että vastausvaihtoehdot taulussa on name kentän lisäksi id kenttä, joka vastaa vastaus.kenttä_id kentän loppuosaa.

Silloin voisi olettaa että haet jotain tälläista:

Koodia: [Valitse]

select vastaus_id, vastaus_data as kenttä_id_85_data, (select name from vastausvaihtoehdot where id=substring(vastaus.kenttä_id,instr(vastaus.kenttä_id,'_')+1))
from vastaus
where lomake_id=85 and kenttä_id like '89\_%'

[JA5U]

http://mikehillyer.com/articles/an-introduction-to-database-normalization/

Juu, tässä on vähä sen ongelma, etten ole tehnyt tuota kantaa... Enkä kyllä yleisestikään ottaen juuri kantoja suunnitellut
Tyypillinen tilanne, kun käytetään avoimen lähdekoodin ratkaisuja, jotka eivät sitten ihan taivukaan.

Kiitos kyselystä, se vei jo merkittävästi lähemmäs haluttua lopputulosta ja oletuksesi osuivat oikeaan
Ongelmana on vielä se, että tuloksissa name sarakkeen arvo ei muutu eli se on kaikilla tulosriveillä sama.
Yritänkin nyt perehtyä tuohon substring ja instr yhdistelmään.

EDIT: Ja en ole ihan varma, että tuleeko tuossa saman vastauksen kentät yhdelle riville?
Eli vastaus_id avulla pitäisi muodostaa yksi rivi nykyisen kahden sijaan.

[retu]

Se oli paras arvaus taulujen rakennetta tietämättä (=vahinko).

Lisää kenttä_id siihen ulompaan select-lauseeseen, niin näet onko se sama molemmilla riveillä. Jos on, alikyselykin palauttaa saman name kentän molemmille riveille.

Onko vaustausvaihtoehdot.id yksilöivä kenttä? Ehkä alikysely palauttaa useamman rivin, jos sama id on useampaan kertaan. Muuta muotoon "(select distinct name from...".

[JA5U]

Se oli paras arvaus taulujen rakennetta tietämättä (=vahinko).

Lisää kenttä_id siihen ulompaan select-lauseeseen, niin näet onko se sama molemmilla riveillä. Jos on, alikyselykin palauttaa saman name kentän molemmille riveille.

Onko vaustausvaihtoehdot.id yksilöivä kenttä? Ehkä alikysely palauttaa useamman rivin, jos sama id on useampaan kertaan. Muuta muotoon "(select distinct name from...".

Vastaustaulun rakenne:

lomake_id	vastaus_id	kentta_id	data
666	1	85	asdasdad
666	1	89	id_123
...	...	...	...

toivottu lopputulos:

vastaus_id	kentta_id_85	kentta_id_89
1	asdasdad	Selkokielinen

Ja em. kentta_id_89 arvo tulisi johtaa vastausvaihtoehdot taulusta:

vaihtoehto_id	name
123	Selkokielinen

[Tomin]

Kuulostaa vähän joltain transpoosilta (transpose) tai PIVOT-operaatiolta. En ole kyllä koskaan käyttänyt SQL:ssä moista.

[JA5U]

Kuulostaa vähän joltain transpoosilta (transpose) tai PIVOT-operaatiolta. En ole kyllä koskaan käyttänyt SQL:ssä moista.

Jottain sellasta. Yllättää kyllä tämä haasteellisuus 

Noh, lataan sen kannan omalle koneelle ja kokeilen säätää tulokset Workbenchillä.

[JA5U]

Ei mikään kaunis ratkaisu eikä tuloskaan ole täysin sitä mitä toivoin.
Nyt saan kuitenkin tarvittavan datan, jota voin vaikka koodissa muokata, kun siinä ei ole oikeastaan muuta vikaa kuin ylimääräisiä sarakkeita.

Koodia: [Valitse]

SELECT
    *
FROM
    (SELECT
        `vastaus_id`, `kentta_id`, `data`
    FROM
        `vastaukset`
    WHERE
        `kentta_id` = 85
            AND CHAR_LENGTH(`data`) > 1) AS `A` // Vain kentät joilla on arvo
        JOIN
    (SELECT
        `vastaus_id`,
            `kentta_id`,
            `data`,
            (SELECT
                    name
                FROM
                    `vastausvaihtoehdot`
                WHERE
                    `id` = SUBSTRING_INDEX(`vastaukset`.`data`, '_', - 1)) AS `name`
    FROM
        `vastaukset`
    WHERE
        `kentta_id` = 89) AS `B` ON `A`.`vastaus_id` = `B`.`vastaus_id`


[retu]

Nuo leikkeet selvitti hieman. 2 ekaa saraketta saat suoraan vastaus taulusta ja kolmannen sillä alikyselyllä, kun korjaa hakuehdon oikeaan kenttään (käyttämällä alikyselyä ):

Koodia: [Valitse]

select name from vastausvaihtoehdot
where vaihtoehto_id=(select substring(vastaus.data, instr(vastaus.data,'_')+1) from vastaus where kenttä_id=59)Mutta sen muuttaminen noin tuo varmaan tolkuttomasti rivejä. Sillä vielä pitäisi tietää mikä yksilöi haettavan rivin. Varmaankin lomake_id ja vastaus_id, joten ne pitää lisätä tuohon sisimmäiseen alikyselyyn. Koska vastaus taulu on kahteen kertaan, sille on käytettävä aliaksia v1 ja v2 että mysql tietää kumpaan viitataan. Lisäksi lomake_id on ehkä lisättävä uloimpaan kyselyyn, jos ainoa kiinnostuksen kohde on tuo pirullinen lomake.

Koodia: [Valitse]

select v1.vastaus_id, v1.data as kentta_id_85,
  (select name
  from vastausvaihtoehdot
  where vaihtoehto_id=
    (select substring(v2.data, instr(v2.data,'_')+1)
    from vastaus v2
    where v2.lomake_id=v1.lomake_id and v2.vastaus_id=v1.vastaus_id and v2.kenttä_id=59)
  ) as kentta_id_89
from vastaus v1
where v1.kenttä_id=85 and v1.lomake_id=666
Muoks! Jaaha, kerkesit jo sorvata pyörän ite.