02. september 2008 - 14:38Der er
18 kommentarer og 1 løsning
Forespørgsel på flere tabeller
Jeg har en database hvor jeg gerne vil hente data fra 5 tabeller
Tabel1 har TranID som primary key
Tabel2 har DetailID som primary key og TransID som foreign key til Tabel1
Tabel3 har Payments som primary key og TranID som foreign key til tabel1
Tabel4 har DetailID som primary key
Tabel5 har SurveyID som Primary key og TranID som foreign key til Tabel1
Der er egentlig ikke nogen data jeg skal bruge i det videre forløb i tabel1 og tabel2. I tabel3 skal jeg bruge data fra kolonnen FOP I tabel4 skal jeg bruge data fra kolonnen VisualID og NodeNo I tabel5 skal jeg bruge data fra kolonnen AnswerText
Her er den tilrettede: select t3.FOP, t4.VisualID, t4.NodeNo, t5.AnswerText from JNLHeaders t1 inner join JNLDetails t2 on t1.JNLTranID = t2.JNLTranID inner join JNLPayments t3 on t1.JNLTranID = t3.JNLTranID inner join JNLTickets t4 on t2.JNLDetailID = t4.JNLDetailID inner join JNLSurveys t5 on t1.JNLTranID = t5.JNLTranID
Jeg får et resultat ud af forespørgslen, men der er noget galt...
Hvis jeg laver en select count(*) from JNLTickets får jeg 82752 som resultat - dette skal også være resultatet af min join.
Hvis jeg kører ovenstående join får jeg kun 26097 hvoraf flere af dem ser ud som om de er der 2 gange...
AnswerText feltet kan godt være tomt men alligevel kan der være noget i de andre felter...
Der går noget galt på en eller anden måde - der er noget dobbelt konfetti...
F.eks. kan jeg finde et bestemt VisualID 2 gange i udtrækket - hvis jeg tjekker dette direkte i tabellen JNLTickets hvor VisualID er, så er den der kun 1 gang.
Og hvis jeg tjekker kolonne answertext i udtrækket har jeg ikke nogen der er tomme - alle er fyldt ud med text...
Hvis jeg tjekker hvor mange rows jeg har i JNLSurveys så er det 9266 - så der burde ikke være flere end 9266 rows i udtrækket hvor der står noget i answertext.
Men jeg får igen præcis 26097 rows ved denne: select t3.FOP, t4.VisualID, t4.NodeNo, t5.AnswerText from JNLTickets t4 inner join JNLDetails t2 on t4.JNLDetailID = t2.JNLDetailID inner join JNLPayments t3 on t2.JNLTranID = t3.JNLTranID inner join JNLSurveys t5 on t2.JNLTranID = t5.JNLTranID
Og godt 21117 ved denne: select distinct t3.FOP, t4.VisualID, t4.NodeNo, t5.AnswerText from JNLTickets t4 inner join JNLDetails t2 on t4.JNLDetailID = t2.JNLDetailID inner join JNLPayments t3 on t2.JNLTranID = t3.JNLTranID inner join JNLSurveys t5 on t2.JNLTranID = t5.JNLTranID
Når du bruger inner join, så fra-filtreres de rækker, hvor der ikke findes en "match" i alle de joinede tabeller. Dvs, hvis der findes en række i tabel1, som hører til en tilsvarende række i tabel2, men der IKKE findes en tilhørende række i tabel3, så vil hele denne række være frasorteret.
Du skal i stedet bruge left outer join, som altid tager de rækker som findes i den venstre tabel (dvs den som står nævnt først i join'en), og HVIS der er et match i den højre tabel, så kobles disse data på - ellers bliver kolonnerne bare NULL.
Hvis du får duplikater, så kan det tyde på, at tabellerne ikke er en en-til-en relation, men en en-til-mange. Hvis der i tabel3 er 2 rækker der passer på en af rækkerne fra tabel5, så vil det samlet set resultere i 2 rækker. Nogle af kolonnerne vil i så fald være forskellige.
Men prøv lige at start med et ændre inner join's til left outer join's, og læg så mærke til om nogle af kolonnerne er forskellige, i de tilfælde hvor du har id'er flere gange.
select t3.FOP, t4.VisualID, t4.NodeNo, t5.AnswerText from JNLHeaders t1 left outer join JNLDetails t2 on t1.JNLTranID = t2.JNLTranID left outer join JNLPayments t3 on t1.JNLTranID = t3.JNLTranID left outer join JNLTickets t4 on t2.JNLDetailID = t4.JNLDetailID left outer join JNLSurveys t5 on t1.JNLTranID = t5.JNLTranID
Det vil sige som den første aaberg_cc lavede - er det den korrekte???
Jeg får nu returneret ca. 325000 rows. Det vil sige ca. 5 gange flere end forventet - og mange af dem er ene NULLs...
Når du joiner 2 tabeller, så kan du få flere rows for en ID, hvis foreign key tabellen har flere rows som peger på primær tabellen. Det er derfor du får duplikater.
Men prøv at tilføje "distinct" til den sidste query, det vil fjerne alle duplikater.
select distinct t3.FOP, t4.VisualID, t4.NodeNo, t5.AnswerText from JNLHeaders t1 left outer join JNLDetails t2 on t1.JNLTranID = t2.JNLTranID left outer join JNLPayments t3 on t1.JNLTranID = t3.JNLTranID left outer join JNLTickets t4 on t2.JNLDetailID = t4.JNLDetailID left outer join JNLSurveys t5 on t1.JNLTranID = t5.JNLTranID
select distinct t3.FOB, t4.VisualID, t4.NodeNo, t5.AnswerText from JNLTickets t4 left outer join JNLDetails t2 on t4.JNLDetailID = t2.JNLDetailID left outer join JNLPayments t3 on t2.JNLTranID = t3.JNLTranID left outer join JNLSurveys t5 on t2.JNLTranID = t5.JNLTranID
Du er nok nødt til at læse lidt om grundlæggende SQL. Hvis du forstår hvordan inner og outer joins virker, vil du let forstå hvad der sker. Men hør her.
Hvis du joiner tickets og details med inner join, så:
- får du kun de tickets med der har en eller flere details - får du ikke bare én række per ticket, men en række pr. detail der er tilknyttet, derfor bliver der flere med samme rækker ticket.
Hvis du joiner tickets og details med outer join, så:
- får du kun alle tickets med, uanset om de har details - får du stadig ikke bare én række per ticket, men en række pr. detail der er tilknyttet, derfor bliver der flere rækker med samme ticket.
Hvis du vil dubletterne til livs skal du bruge enten DISTINCT, eller GROUP BY, især den sidste er smart, men du kan ikke hente data fra de rækker der 'forsvinder'. Læs på det.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.