Hjælp til query med max() og join

Problemet centrerer sig muligvis om tabel tilbud, hvor du har tilbuddets identitet og revision i samme række.  Så er dine data ikke 'normaliseret,' og det giver problemer ved queries. 

Der burde være en tabel Tilbud med de grund oplysninger om tilbuddet såsom tilbuddets titel og kontaktperson.  Dertil en særskilt tabel Indhold hvor du har priser, leverancer, og lignende, det som forandres i hver revision.  Plus din tabel med aftaler, der så er linket til aftalen selv, ikke til en af revisionerne.  Når du så skal til at lave et nyt tilbud udfylder du med det samme en ny række i tabellen Tilbud med 'hvornår hvor hvem hvad hvorfor' men ikke priser og lignende.  Når du så er klar med første version af priser og betingelser fylder du det i tabellen Indhold som 'revision' nul.  Så kan du, for eksempel for tilbud nummer 27, få aftalens grund oplysninger, aftaler, og seneste indhold trukket ud med en query som denne:

SELECT t.titel, a.aftale, a.vt, i.* FROM Tilbud t JOIN Aftaler a ON t.id = a.tilbudid JOIN Indhold i ON t.id = i.tilbudid WHERE t.id = 27 AND i.rev =(SELECT MAX(rev) FROM Indhold WHERE tilbudid = 27)

Ikke for at anfægte dit indlæg, for det er et fint og godt indlæg, men det gør jeg så alligevel... ;-)

Du har jo heller ingen forudsætningfor at kende historikken bag spørgsmålet, så det får du lige.

Det er et driftssystem, som efterhånden har nogle år på bagen. Jeg er vist onkring den 7 udvikler på systemet, som oprindelig var lavet til administration af MUS-samtaler. Inden jeg startede, blev det så besluttet at "udvide" det som et salgs- og ordresystem. Databasen er derfor ikke altid lige logisk opbygget, og sikkert ikke normaliseret helt korrekt, men i dette tilfælde vil jeg mener, det er et tvivlsspørgsmål, om revision skal ud på en anden tabel eller ej.

Lad mig uddybe databaseopbygningen.

Tabellen Tilbud består af:
id, tilbud, rev, løbenr, oprettet, status, ordre og afdeling

Altså kun de grundlæggende ting for tilbuddet.

Derudover har jeg 6 andre tilbudstabeller, navngivet tilbud0, tilbud1, tilbud2 osv.
Heri ligger alle oplysninger om specifikke oplysninger om tilbuddet, såsom pris, opbygning, beregningstal o.l. Hvert felt er navngivet som "r" efterfulgt af et nummer. Alle felter der er under 100, ligger så i tilbud0, alle felter fra 100 til 199 ligger i tilbud1 og så fremdeles.
I hver tabel er der et tilbudsnummer, der referer til id'et i tabellen tilbud.

Jeg har derfor lidt svært ved at se, hvad det skulle gøre godt for at ligge rev i en anden tabel, eventuelt en tabel for den selv.

Som jeg ser det ligger problemet i, at alle aftaler bliver tilknyttet revision 0, og ikke den aktuelle revision aftalen laves på. Beslutningen om dette ligger også flere år tilbage, og jeg har fået forklaret, at det skyldes at brugerne ville kunne se alle aftaler på alle tilbudsrevision, ligegyldig hvilken de var oprettet på. Det kunne så godt være løst på en mere elegant måde, men det virker, og vil være for tungt at skulle ændre på nu.

Så jeg skal have fundet en måde at få trukket data ud om den seneste revision, samtidig med at jeg kigger på den første revision for at få aftalerne med.

Det skal nemlig munde ud i en oversigt, over de tilbud som brugerne mener er varme.

Men måske jeg ikke helt har forstået dit indlæg.

Så du vælger, for nu, ikke at forholde dig til den historiske ikke-normalisering, der blandt andet resulterer i, at i må gemme noget der er fælles for alle revisioner, forsamlingen af aftaler, under en af revisionerne.  I stedet søger du efter en 'work-around'.

Og det kan lade sig gøre, hvis der er noget der unikt identificerer et tilbud uanset revision.  Er feltet 'tilbud' i tabellen Tilbud unik, således at det for eksempel hedder 'Ting_og_Sager' for alle revisioner, ikke for eksempel 'Ting_og_sager' (med lille s) og aldrig genbruges, således at tilbud på en udvidelse ville hedde, for eksempel, 'Ting_og_Sager2'.  Hvis 'tilbud' er en garanteret unik reference til et tilbud kunne i vel få sidste revision plus de varme aftaler for tilbud 'Ting_og_Sager' således:

SELECT t.*, a.aftale FROM Tilbud t JOIN Aftale a ON a.tilbudid = (SELECT id FROM Tilbud WHERE tilbud = 'Ting_og_Sager' AND rev = 0) WHERE t.tilbud = 'Ting_og_Sager' AND rev = (SELECT MAX(rev) FROM Tilbud WHERE tilbud = 'Ting_og_Sager' AND a.vt = TRUE);

Det er snørklet, men det bliver det som regel for sådanne 'work-arounds'.  Hvis du henter det fra en applikation vil du naturligvis putte 'Ting_og_Sager' i en variabel.

Men tilbage til teorien.  Jeg forstår tabellen Tilbud således: 

Hvis for tre måneder siden en kunde bad om tilbud til at lave en Ting_og_Sager, så oprettede i en række i Tilbud, for eksempel

id tilbud rev o.s.v
25 Ting_og_Sager 0 o.s.v.

Da i så fremsendte jeres første tilbud, med detaljer i de andre tilbudstabeller, sagde kunden, at han hellere ville have grønne dimser og røde dupper.  Så i gik igang igen, startende en ny række i Tilbud så som:

id tilbud rev
32 Ting_og_Sager 1 o.s.v.

og så videre med 37 Ting_og_Sager 2 o.s.v. og 45 Ting_og_Sager 3 o.s.v..

I har således ingen steder hvor i kan gemme de grundlæggende ting for et tilbud der er fælles for alle revisionerne.  Når i så alligevel skal have gemt noget der er fælles for alle revisionerne, så som forsamlingen af aftaler, så må i knytte det til en af revisionerne, revision 0.

Har jeg forstået det korrekt? 

Hvis ikke, og i alligevel har en tabel med de grundlæggende oplysninger der ikke ændrer sig fra revision til revision, så vis mig den, ud afinteresse nu da vi er kommet så langt.  Og så ville mit forslag være en engangs update af aftale tabellen hvor i knytter alle aftaler til tilbuddet selv i stedet for til revision 0. 

Hvis det er som jeg har forstået, at i ikke har en tabel for tilbuddets grundoplysninger, så er det, i teorien om Relational Databaser, en strukturel svaghed der kan forårsage en række problemer, såsom det at skulle gemme en fælles aftale forsamling under en af revisionerne.

Så du vælger, for nu, ikke at forholde dig til den historiske ikke-normalisering, der blandt andet resulterer i, at i må gemme noget der er fælles for alle revisioner, forsamlingen af aftaler, under en af revisionerne.  I stedet søger du efter en 'work-around'.

Nemlig

Og det kan lade sig gøre, hvis der er noget der unikt identificerer et tilbud uanset revision.  Er feltet 'tilbud' i tabellen Tilbud unik, således at det for eksempel hedder 'Ting_og_Sager' for alle revisioner, ikke for eksempel 'Ting_og_sager' (med lille s) og aldrig genbruges, således at tilbud på en udvidelse ville hedde, for eksempel, 'Ting_og_Sager2'.  Hvis 'tilbud' er en garanteret unik reference til et tilbud kunne i vel få sidste revision plus de varme aftaler for tilbud 'Ting_og_Sager' således:

Er ikke helt med på hvad du mener, der skal være unikt.
Altså id er selvfølgelig unikt, så det repræsentere det enkelte tilbud med revision.
Tilbud er selve tilbudsnummeret, og det er om jeg så må sige "semi-unikt", idet en sag kun kan have et tilbud. Sagen kan dog godt bestå af flere tilbudsrevisioner.
Blev det mere klart af den forklaring?

SELECT t.*, a.aftale FROM Tilbud t JOIN Aftale a ON a.tilbudid = (SELECT id FROM Tilbud WHERE tilbud = 'Ting_og_Sager' AND rev = 0) WHERE t.tilbud = 'Ting_og_Sager' AND rev = (SELECT MAX(rev) FROM Tilbud WHERE tilbud = 'Ting_og_Sager' AND a.vt = TRUE);

Et hurtigt blik, så ser det meget fornuftigt ud, skal lige prøve det af. Måske jeg løber ind i problemer, når jeg skal have joinet de andre tilbudstabeller... Vender lige tilbage når jeg har testet det.

Men tilbage til teorien.  Jeg forstår tabellen Tilbud således: ..........

Ja, du er rigtigt på den, og som du forklarer det der, er kan jeg godt se problemet. Det er så bare først nu, vi reelt løber ind i en problemstilling derom. Åhhh, hvad man dog ikke arver engang imellem... ;-)

Hvis det nu skulle have været korrekt normaliseret, hvordan skulle tabellerne så have været bygget op? Hvis der skulle komme 2 torsdage i en uge, og jeg ville få tid til at rette det.

Skulle rev ud i sin egen tabel såsom:
revid revnr tilbudsid

I tilbudstabellen burde jeg vel så kunne nøjes med en record for hele grundtilbuddet.

Hvis jeg udskifter "Ting_og_Sager" i din kode, med tilbudsnummeret, så får jeg det varme tilbud ud. Men det kræver at jeg kender tilbudsnummeret.

Når jeg så har en query der allerede trækker data ud fra alle de andre data ud, så kan jeg selvfølgelig lave din query med det tilbudsnummer jeg får fra den forrige.

Men kan det kringles, så jeg kan lave hele hugget i en query?

Og tilbage til teorien, man vil jo gerne lære noget, hvis det er muligt.

Jeg kom til at fundere over aftale tabellen, hvordan skal den opbygges? Skal den bare referere til det grundlæggende tilbud og hvordan kan jeg eventuelt håndtere aftaler på revisionerne?
Puha, synes det er ved at køre rundt i hovedet på mig, det kalder vist på et E/R-diagram, så man kunne få visualiseret det tydeligere

Du siger, at et tilbud kun har et tilbudsnummer, således at tilbud 'Ting_og_Sager' rev0 har, for eksempel, tilbudsnummer 1234, 'Ting_og_Sager' rev1 har også tilbudsnummer 1234, og 'Ting_og_Sager' rev2 har tilbudsnummer 1234 o.s.v. Så du har svaret på hvad der unikt identificerer et tilbud på tværs af alle revisionerne.  Fortæl mig nu hvordan man finder dette unikke tilbudsnummer.  I en anden tabel?  Den oplysning er muligvis nøglen til at løse det umiddelbare problem og til at forøge normaliseringen i tabelstrukturen.

Ja, jeg tror vi er ved at forstå hinanden, men lige for at klarlægge det endnu mere, så synes jeg lige vi skal fjerne Ting_og_Sager. Det forvirrer i hvert fald mig en gang imellem.

Et tilbud kan hedder 45000R0.

• 45000 kommer fra feltet tilbud i tabellen tilbud.
• R kommer hardcodet i navnet.
• 0 kommer fra rev i tabellen tilbud.

Kommer der så en revision af ovenstående tilbud kommer det til at hedder 45000R1 og så fremdeles.

De har dog hver deres unikke (primær nøgle) på tilbudstabellen som er id.

På tabellen aftaler, har være række en reference til et tilbud via tilbudid. Problemet er dog at den refererer til 45000R0 og ikke den aftale er oprettet på.

Så forstår jeg dig ret, må selve tilbudsnummeret, i dette tilfælde 45000, være det unikke tværidentifikation

Skær det lige ud i pap - jeg skal lige forstå tabellen tilbud og dens felter.  I  #2 siger du dette:

id, tilbud, rev, løbenr, oprettet, status, ordre og afdeling

Hvis det holder stik, hvilke værdier er der så, for eksempel, i tilbud, hvis man har afgivet et tilbud og lavet revision.  Er de følgende værdier typiske?

id, tilbud, rev, løbenr, oprettet, status, ordre og afdeling
25 45000 0 1234 10-11-2010 xx yy zz
32 45000 1 2345 10-12-2010 xx yy zz

eller snarere

id, tilbud, rev, løbenr, oprettet, status, ordre og afdeling
25 45000R 0 1234 10-11-2010 xx yy zz
32 45000R 1 2345 10-12-2010 xx yy zz

??

I begge tilfælde er tilbuds id for revision 0 = 25, ikke sandt.

Hvad står derså i tabellen aftaler?  I dit oprindelige spørgsmål gav du dette:

id, tilbudid, aftale, vt

Så hvilke værdier står der typisk i tabellen aftale for aftaler der vedrører tilbud 45000 (eller tilbud 45000R)?  Kan det være sådan noget?

id tilbudsid aftale vt
15 25 aftaleXYZ varm
27 25 aftaleBBB varm
55 25 aftale DEF -

Ja, det er næsten helt rigtigt forstået.

Tabellen tilbud se ud sådan

id, tilbud, rev, løbenr, oprettet, status, ordre og afdeling
25  45000    0      0    10-11-2010   xx    yy        zz
32  45000    1      0    10-12-2010   xx    yy        zz

Eneste ændring er løbenr, men det bliver ikke brugt i lige dette henseenden. Der er 2 afdelinger, som bruger systemet, og kun den ene bruger begrebet løbenr.

Og tabellen aftaler, se også næste ud som du skriver, dog kan der kun være en varm aftale pr. tilbud.

id tilbudsid     aftale     vt
15    25        aftaleXYZ   1
27    25        aftaleBBB   0
55    25        aftale DEF  0 

Det var godt at få afklaret.  Så tilbage til #6.  Du siger, i realiteten, at min kode i #3 virker, blot ved at du udskifter mit fantasi tilbudsnavn med det virkelige tilbudsnavn, for eksempel 45000.  Har jeg forstået det rigtigt?  I så fald er det vel et resultat!  Du kan for et tilbud få udskrevet seneste revision plus aftalerne. 

Men det rejser så et nyt problem, siger du, nemlig at når du står og skal udskrive det, så kender du ikke tilbuds'navnet' (så som 45000.)  Hvis du skal bruge min foreslåede kode skal du først søge efter tilbuds'navnet'.

Ok, hvad kender du så i det øjeblik du skal have det udskrevet?  Eller er opgaven ikke at udskrive varme aftaler for et enkelt tilbud, men at udskrive alle tilbud (sidste revision) med varme aftaler?  I så fald er det blot et spørgsmål om i koden #3 at fjerne begrænsningen til et enkelt tilbud, således:

SELECT t.*, a.aftale FROM Tilbud t JOIN Aftale a ON a.tilbudid = (SELECT id FROM Tilbud WHERE tilbud = t.tilbud AND rev = 0) WHERE rev = (SELECT MAX(rev) FROM Tilbud WHERE tilbud = t.tilbud) AND a.vt = 1;

Det er lidt høne og ægget problematikken.

Opgaven er at udskrive alle tilbud, der er varme. Et tilbud kan kun have en aftale der er varm.

Kører jeg

SELECT t.*, a.aftale FROM Tilbud t JOIN Aftaler a ON a.tilbudid = (SELECT id FROM Tilbud WHERE tilbud = t.tilbud AND rev = 0) WHERE rev = (SELECT MAX(rev) FROM Tilbud WHERE tilbud = t.tilbud) AND a.vt = 1

Får jeg at vide, at der er en Subquery som returnere mere end 1 række. Jeg skal lige have tjekker hvilken.

Men måske jeg kan lave et udtræk først, hvor jeg får alle tilbud ud, så har jeg jo tilbudsnummeret, så kan jeg vel bagefter lave et nyt udtræk, men hvert tilbudsnummer som parameter og her i fjerne alle ikke varme tilbud.

Lidt omvendt rækkefølge, og ville da helst kunne gøre det i et hug, hvis det er muligt

Så opgaven er at udskrive sidste revision og den varme aftale for alle tilbud der har en varm aftale.  Det var også godt at få afklaret dette.

Jeg kikker på det med subquery der returnerer mere end 1 række.  Det fik jeg ikke i den testdatabase jeg lavede.  Jeg har lidt travlt ligenu, så måske senere på dagen eller i morgen.

Det synes jeg ellers jeg forklarede i det første indlæg/spørgsmålet, men det kan selvfølgelig misforståes.

Hmm, måske der så er en fejl i databasen, hvis du ikke får fejlen. Hvis bare jeg kune finde ud af hvad den brokker sig over.

Jeg prøvede lige at lave en fejl i min test database, således at der for en aftale var to rækker med rev = 0, og så fik jeg fejlmeldingen, at 'Subquery returns more than 1 row'.  Så jeg vil vædde på, at du har aftaler med flere end en rev 0.  Du kan sikkert finde dem ved sådan en query:

SELECT tilbud, COUNT(*) antal FROM Tilbud WHERE rev = 0 GROUP BY tilbud HAVING antal > 1;

Jeg kommer iøvrigt af tidshensyn til at slutte her.  Jeg opretter dette som svar, og du afgør hvorvidt jeg har hjulpet og skal have mit svar accepteret.  I modsat fald må du selv oprette et svar og acceptere det.

Isskårup, så blev spørgsmålet en uge gammelt, og der har ikke været aktivitet siden 12 oktober.  Kan jeg bede dig lukke spørgsmålet?  Hvis du ikke mener (eller ikke er sikker på) at jeg har været til hjælp, så opret selv et svar og accepter det.  Eller forventer du friske indlæg fra andre medlemmer?  Det er nok ikke så sandsynligt nu spørgsmålet er kommet 'under horizonten' på side 10 af spørgsmål.

Rolig nu, har i dag fået tjekket, hvad der skyldtes den seneste fejl jeg fik, og det var rigtigt nok flere af samme slags på rev 0. De skulle være ryddet op nu.

I morgen kan jeg så kigge på, om resten passer.

Mon ikke du overlever, det er trods alt mit spørgsmål, og mig som ikke får løst problemet, hvis jeg ikke selv holder det i live...

Så  skulle jeg mene jeg endelig har fået testet det hele, og det er lagt i drift.

Tak for hjælpen.