SQL query. 2 tabellen samenvoegen in één lijst

[Dennis013]

Deze query gaat me echt boven me pet. Ik weet wel hoe ik met joins ed. moet werken maar deze is net wat te ingewikkeld. Hopelijk heeft iemand een idee voordat ik aan de slag ga met allerlei ingewikkelde replace constructies in asp.

Het betreft een 60 multiple choice vragenlijst die door 2 personen ingevuld dient te worden. Namelijk door jezelf en door iemand anders, die het over jou invult. Deze test kan ook meerdere keren gedaan worden, daarom ben ik met gebruikersessies gaan werken.
Die test is nu helemaal gemaakt en werkt prima, maar nu moet ik een een overzicht per klas kunnen maken.

Ik heb de volgende tabellen.

[Tbl_zelfbeeld] Ingevuld door de persoon zelf
-id
-antwoord (gegeven antwoord 0 -100)
-user_id
-naam
-onderwerp
-vraag_id
-sessie (unieke sessie)
-datum
-klas

[Tbl_invite]
- id
-antwoord (gegeven antwoord 0 -100)
- user_id
- naam
- onderwerp
- vraag_id
- sessie (unieke sessie)
- user_mail
- datum
- klas

Door de sessie is die antwoordenreeks van de gebruiker dus herkenbaar in het systeem. Op de een of andere moet ik uit de twee bovenstaande tabelen, het onderstaande overzicht krijgen.

| naam | beoordeeld door | antw 1 tm antw 60 |

Hoe pak ik dat aan?

[4of9]

waar ligt de relatie tussen tabel1 en tabel2?

[Dennis013]

4of9 schreef op donderdag 28 juni 2007 @ 11:23:
waar ligt de relatie tussen tabel1 en tabel2?

Bij de sessie

[lier]

Kan je eens exact uitleggen hoe je tot dit datamodel gekomen bent ?
Kan je aangeven hoe je de antwoorden opslaat ?
Misschien mogelijk om wat voorbeeld inhoud te tonen ?

[Verwijderd]

Je zult een extra tabel moeten maken waarin alle antwoorden staan met een userid en session id, deze kun je dan "LEFT" joinen op je andere tabellen. Wat je nu gaat doen is data herhalen (ambigious) zet users, alle klassen, alle tests en alle antwoorden elk in een apparte tabbel.

Je zult zien dat je zo meer overzicht en structuur krijgt. Database ontwerp is een van de belangrijkste dingen, dit word niet voor niets al zeer vroeg in de ontwerp fase al gedaan.

[ Voor 15% gewijzigd door Verwijderd op 28-06-2007 11:32 ]

[Dennis013]

lier schreef op donderdag 28 juni 2007 @ 11:27:
Kan je eens exact uitleggen hoe je tot dit datamodel gekomen bent ?
Kan je aangeven hoe je de antwoorden opslaat ?
Misschien mogelijk om wat voorbeeld inhoud te tonen ?

Hierbij een voorbeeldje van de tabellen. (MySQL trouwens)



Het datamodel heb ik zelf bedacht, leek me het meest logisch en de antwoorden zijn allemaal als 'text' opgeslagen

[Dennis013]

Verwijderd schreef op donderdag 28 juni 2007 @ 11:31:
Je zult een extra tabel moeten maken waarin alle antwoorden staan met een userid en session id, deze kun je dan "LEFT" joinen op je andere tabellen. Wat je nu gaat doen is data herhalen (ambigious) zet users, alle klassen, alle tests en alle antwoorden elk in een apparte tabbel.

Je zult zien dat je zo meer overzicht en structuur krijgt. Database ontwerp is een van de belangrijkste dingen, dit word niet voor niets al zeer vroeg in de ontwerp fase al gedaan.

Helaas is het vaak het geval dat je niet altijd alles van tevoren kunt bepalen. De klant heeft vaak gaande weg het proces allerlei ideeen en eisen. Alles in één tabel is opzich wel een goed idee.. Maar is dat de enige manier om het zo te doen??
Ik kan hier niet al te veel tijd mee kwijt zijn eigenlijk..

[lier]

Misschien kan je naar de reactie van j.ostie kijken.

Het datamodel lijkt misschien logisch maar is in ieder geval database technisch nog niet helemaal optimaal.

Moet het trouwens vanuit een query gepresenteerd worden ?
Of heb je een GUI waarin een en ander getoond gaat worden ?

Dennis013 schreef op donderdag 28 juni 2007 @ 11:47:Helaas is het vaak het geval dat je niet altijd alles van tevoren kunt bepalen. De klant heeft vaak gaande weg het proces allerlei ideeen en eisen. Alles in één tabel is opzich wel een goed idee.. Maar is dat de enige manier om het zo te doen??
Ik kan hier niet al te veel tijd mee kwijt zijn eigenlijk..

Aardig dat je over klanten begint en voortschrijdend inzicht komt inderdaad erg vaak voor. Echter heeft dat helemaal niets met jouw oplossing te maken...

Overigens is de oplossing niet alles in één tabel, maar om de vragen los te trekken uit jouw tabellen. Verder zijn jouw tabellen identiek, kan je beter kiezen voor één tabel met als extra veld een identifier of het om een zelfbeeld of om een inzicht gaat.

Welke opleiding doe je ?
Is het (nog) mogelijk om het datamodel volledig om te gooien ?

[ Voor 61% gewijzigd door lier op 28-06-2007 11:52 ]

[Verwijderd]

Dennis013 schreef op donderdag 28 juni 2007 @ 11:47:
Maar is dat de enige manier om het zo te doen??

Hoe zou je de huidige tabellen dan samen willen voegen? M.a.w.: Aan welke voorwaarden moeten de tabellen volgens jou voldoen om te kunnen bepalen dat twee records bij elkaar horen?

[4of9]

Ik zou zelf ook het datamodel wat anders op hebben gezet. Het probleem dan blijft dat je alle antwoorden op 1 regel wilt hebben. Dit is erg lastig voor elkaar te krijgen en zul je al dan niet met stored procedures / temptables en/of views aan de slag moeten.

Misschien is het gewoon beter om het in je programma code op te lossen (het zal tenslotte presentatie data zijn).

[Verwijderd]

Het probleem is dat je met jou huidige data model niet veel kunt doen met queries, alles staat in één record. Als databases ergens voor zijn is het voor het bewaren van de integeriteit van data, door gebruik van een goed datamodel en toegepaste relaties word de programeur deze stap uit handen genomen.

Verder blijf ik zeggen dat het vaker voor laten komen van data echt tegen één core principe van DB's ingaat, DB's zijn ontwikkeld juist om dat soort dingen te voorkomen.

Er zijn grenzen van quik en dirty, als je altijd zo te werk gaat zit je binnen een half jaar met een heel onpretting systeem. Ik raad je aan om je data model te herzien nu het nog niet al te veel werk is. I zou willen dat ik je dit soort horror kan besparen maar mayb moet je het zelf minstens één keer mee maken.

Verder eerlijkheid duurt het langst, je kunt beter de klant nu 4 uur extra arbeid laten betalen dan dat jij en jou bedrijf later UUUUUUUUREEN aan extra "niet factureerbare" tijd kwijt zijn omdat je je klant niet kunt verklaren waarom alles zo veel werk is. Deel je klant op een tactische wijze mee dat het gewoon een paar uur langer duurt.

[ Voor 17% gewijzigd door Verwijderd op 28-06-2007 12:00 ]

[Dennis013]

lier schreef op donderdag 28 juni 2007 @ 11:48:
Misschien kan je naar de reactie van j.ostie kijken.

Het datamodel lijkt misschien logisch maar is in ieder geval database technisch nog niet helemaal optimaal.

Ik heb geen technische opleiding gevolgd, maar ben van huis uit vormgever. Heb mezelf ASP enzo geleerd, maar daardoor mis ik een formele opleiding waar ik alles over databases had kunnen leren.

Moet het trouwens vanuit een query gepresenteerd worden ?
Of heb je een GUI waarin een en ander getoond gaat worden ?

Maakt me niet zoveel uit. Het is nodig voor analyse achter-de-schermen, dus er hoeft geen GUI omheen persé

Overigens is de oplossing niet alles in één tabel, maar om de vragen los te trekken uit jouw tabellen. Verder zijn jouw tabellen identiek, kan je beter kiezen voor één tabel met als extra veld een identifier of het om een zelfbeeld of om een inzicht gaat.

Klopt...

Is het (nog) mogelijk om het datamodel volledig om te gooien ?

Nou liever niet... Het mag niet veel tijd meer kosten, maar ik ben er nu al anderhalve dag mee bezig

[Dennis013]

Verwijderd schreef op donderdag 28 juni 2007 @ 11:50:
[...]
Hoe zou je de huidige tabellen dan samen willen voegen? M.a.w.: Aan welke voorwaarden moeten de tabellen volgens jou voldoen om te kunnen bepalen dat twee records bij elkaar horen?

De sessie is de bindende factor van de twee tabbelen.

[Dennis013]

4of9 schreef op donderdag 28 juni 2007 @ 11:57:
Ik zou zelf ook het datamodel wat anders op hebben gezet. Het probleem dan blijft dat je alle antwoorden op 1 regel wilt hebben. Dit is erg lastig voor elkaar te krijgen en zul je al dan niet met stored procedures / temptables en/of views aan de slag moeten.

Misschien is het gewoon beter om het in je programma code op te lossen (het zal tenslotte presentatie data zijn).

Ik denk dat dat ook de enige oplossing nu is.. Dat met die antwoorden allemaal naast elkaar moeten staan is echt lastig.


--- Mijn excuses dat ik niet alle quotes in één antwoord zet. Hoe delete ik een reactie??

[ Voor 7% gewijzigd door Dennis013 op 28-06-2007 12:13 ]

[P.O. Box]

ik neem aan dat je zoiets wilt:

| naam | beoordeeld door | antw 1 | antw 2 | antw 3 | antw 4 | antw 5 | etcetera
| Edward | Dennis | 10 | 10 | 10 | 10 | 20 | .....
| Edward | Edward | 100 | 100 | 100 | 100 | 100 | ....

(dus beoordeling van Edward door Dennis, en beoordeling Edward door zichzelf... en dat voor een hele klas)...
de oplossing hiervoor zijn PIVOT tables... helaas, dat gaat je in MySQL niet lukken, tenzij je zelf een stored procedure gaat schrijven... of je moet 60 joins gaan maken... maar als je ooit het aantal vragen gaat wijzigen dan kun je dat ook weer gaan veranderen...

Maar ik neem aan dat je het overzicht wilt tonen in een mooie GUI... ik gok met php... dan kun je toch d.m.v. een loop meerdere queries uitvoeren en zo je resultaten mooi op het scherm krijgen...

dus pseudo:
in sql: selecteer beoordeelden en beoordelers van een klas, sessie from table
loopen op het resultaat en per loop in sql weer:
selecteer gegeven antwoorden van beoordeelde, beoordeler en sessie from table

[Verwijderd]

Dennis013 schreef op donderdag 28 juni 2007 @ 12:11:
De sessie is de bindende factor van de twee tabbelen.

Ja dat begrijp ik, want dat heb je al gezegd. Maar schrijf eens in pseudo code de query uit. Dat was logischerwijs het doel van de vraag.

[JKVA]

Ik weet niet helemaal waar je naartoe wilt, maar is UNION de oplossing? Weet ook niet of jouw specifieke DBMS het aankan trouwens.

[P.O. Box]

JKVA schreef op donderdag 28 juni 2007 @ 12:53:
Ik weet niet helemaal waar je naartoe wilt, maar is UNION de oplossing? Weet ook niet of jouw specifieke DBMS het aankan trouwens.

offtopic:
ahum, heb je alleen de titel gelezen?

[Dennis013]

Edwardvb schreef op donderdag 28 juni 2007 @ 12:13:
ik neem aan dat je zoiets wilt:

| naam | beoordeeld door | antw 1 | antw 2 | antw 3 | antw 4 | antw 5 | etcetera
| Edward | Dennis | 10 | 10 | 10 | 10 | 20 | .....
| Edward | Edward | 100 | 100 | 100 | 100 | 100 | ....

Inderdaad. Dat is wat ik wil bereiken.

Edwardvb schreef op donderdag 28 juni 2007 @ 12:13:

(dus beoordeling van Edward door Dennis, en beoordeling Edward door zichzelf... en dat voor een hele klas)...
de oplossing hiervoor zijn PIVOT tables... helaas, dat gaat je in MySQL niet lukken, tenzij je zelf een stored procedure gaat schrijven... of je moet 60 joins gaan maken... maar als je ooit het aantal vragen gaat wijzigen dan kun je dat ook weer gaan veranderen...

Dat is jammer dat dat pivot niet kan... Ik kende dat niet.. Ik zal daar eens wat meer over gaan lezen of ik daar iets terug van kan vinden.

Edwardvb schreef op donderdag 28 juni 2007 @ 12:13:

Maar ik neem aan dat je het overzicht wilt tonen in een mooie GUI... ik gok met php... dan kun je toch d.m.v. een loop meerdere queries uitvoeren en zo je resultaten mooi op het scherm krijgen...

dus pseudo:
in sql: selecteer beoordeelden en beoordelers van een klas, sessie from table
loopen op het resultaat en per loop in sql weer:
selecteer gegeven antwoorden van beoordeelde, beoordeler en sessie from table

Een mooie GUI hoeft niet persé en ik doe het met ASP.... Dat stukje pseudo code begrijp ik niet helemaal in één keer, maar ik zal het goed gaan bekijken... Tis momenteel mijn enigste opte.

[Verwijderd]

als het quick and dirty moet zou ik inderdaad voor Edwardvb's manier gaan. Je doet het dan in twee query's ipv 1. minder netjes en snel maar het is wel makkelijk te implementeren en snel.

Een mooie oplossing is natuurlijk het datamodel overhoop halen en alle overbodigheden eruit filteren. Er staan nu dingen dubbel in de database.

Je moet dus kiezen of je er nu wat extra tijd in stopt en een mooi datamodel opzet, of later tegen nog meer beperkingen aanloopt. Hoe lang je wacht hoe meer weer het aanpassen is natuurlijk.

Hier is in ieder geval wat pseudo voor de quick and dirty manier:

code:

SELECT Tbl_zelfbeeld.id, Tbl_zelfbeeld.naam, Tbl_invite.naam WHERE Tbl_zelfbeeld.sessie = Tbl_invite.sessie

for (elke record uit de query) {
    print naam + naam beoordeeld door

    SELECT antwoord FROM Tbl_zelfbeeld WHERE id = id_uit_record

    print antwoorden + begin op een nieuwe regel
}