Kirjoittaja Aihe: RATKAISTU: SQL kysely - taulujen yhdistäminen SUBSTRING_INDEX-funktion arvolla  (Luettu 3620 kertaa)

JA5U

  • Käyttäjä
  • Viestejä: 462
    • Profiili
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?
« Viimeksi muokattu: 02.04.18 - klo:13.02 kirjoittanut JA5U »

retu

  • Käyttäjä
  • Viestejä: 949
    • Profiili
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\_%'
« Viimeksi muokattu: 02.04.18 - klo:00.14 kirjoittanut retu »

JA5U

  • Käyttäjä
  • Viestejä: 462
    • Profiili
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 :D
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.
« Viimeksi muokattu: 02.04.18 - klo:09.25 kirjoittanut JA5U »

retu

  • Käyttäjä
  • Viestejä: 949
    • Profiili
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

  • Käyttäjä
  • Viestejä: 462
    • Profiili
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_idvastaus_idkentta_iddata
666185asdasdad
666189id_123
............


toivottu lopputulos:
vastaus_idkentta_id_85kentta_id_89
1asdasdadSelkokielinen


Ja em. kentta_id_89 arvo tulisi johtaa vastausvaihtoehdot taulusta:
vaihtoehto_idname
123Selkokielinen

Tomin

  • Palvelimen ylläpitäjä
  • Käyttäjä / moderaattori+
  • Viestejä: 11433
    • Profiili
    • Tomin kotisivut
Kuulostaa vähän joltain transpoosilta (transpose) tai PIVOT-operaatiolta. En ole kyllä koskaan käyttänyt SQL:ssä moista.
Automaattinen allekirjoitus:
Lisäisitkö [RATKAISTU] ketjun ensimmäisen viestin aiheeseen ongelman ratkettua, kiitos.

JA5U

  • Käyttäjä
  • Viestejä: 462
    • Profiili
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

  • Käyttäjä
  • Viestejä: 462
    • Profiili
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

  • Käyttäjä
  • Viestejä: 949
    • Profiili
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ä ;D):
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.
« Viimeksi muokattu: 02.04.18 - klo:13.06 kirjoittanut retu »