23. august 2004 - 22:07Der er
16 kommentarer og 1 løsning
Avanceret SQL query
Her er en lille ekspertnød til dem, som har styr på deres SQL (her taler vi god gammeldags Access og ikke noget fancy MS SQL el. lign. :)
Til at starte med vil jeg give et simpelt eksempel på mit problem, og se om et forslag til løsningen på dette kan overføres til det virkelige problem (som omfatter en del flere tabeller og felter), eller om jeg senere hen må blive nødt til at spørge noget mere.
** TABELLER
"tblAnnonce" med felterne: - id (autonummer, primary key) - overskrift (tekst) - type (tal, henviser til et id fra tblType)
"tblBillede" med felterne: - id (autonummer, primary key) - annonce (tal, henviser til et id fra tblAnnonce) - filnavn (tekst) - hovedbillede (boolean)
"tblType" med felterne: - id (autonummer, primary key) - navn (tekst)
** ET EKSEMPEL
tblType: --------- id navn 1 type1 2 type2
tblAnnonce: ------------- id overskrift type 1 an1 2 2 an2 1 3 an3 1
Den ønskede SQL query skal returnere en liste over annoncer med følgende data: 1. Annoncens id og overskrift 2. Annoncens type (navnet på denne) 3. Antal billeder der henviser til annoncen 4. Filnavnet på det billede der henviser til annoncen med værdien hovedbillede = True MEN!! Hvis ingen af billederne, der henviser til annoncen, har værdien hovedbillede = True, så skal den bare tage "det første og bedste" billede der henviser til annoncen, dvs. billedet med lavest id.
** RESULTAT
id overskrift type antalbilleder filnavn 1 an1 type2 2 billede3.jpg 2 an2 type1 2 billede2.jpg 3 an3 type1 0 (null)
Som I kan se, får vi "billede3.jpg" til annonce 1, da ingen af billederne til denne annonce har værdien hovedbillede = True, og billede 3 er derfor "det første og det bedste". Derimod får vi "billede2.jpg" til annonce 2, da dette har værdien hovedbillede = True.
** LØSNINGSFORSLAG
For god ordens skyld vil jeg lige komme med et løsningsforslag, så I kan tage den derfra.
SELECT tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, COUNT(tblBillede.annonce) AS AntalBilleder FROM (tblAnnonce INNER JOIN tblType ON tblAnnonce.type = tblType.id) LEFT JOIN tblBillede ON tblBillede.annonce = tblAnnonce.id GROUP BY tblAnnonce.id, tblAnnonce.overskrift, tblType.navn
Ovenstående klarer punkt 1-3, men løsningen af punkt 4 kan jeg ikke gennemskue. Det håber jeg I kan :)
Jeg er ikke sikker på det virker, men jeg prøve følgende:
SELECT tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, COUNT(tblBillede.annonce) AS AntalBilleder, first (filnavn) FROM (tblAnnonce INNER JOIN tblType ON tblAnnonce.type = tblType.id) LEFT JOIN tblBillede ON tblBillede.annonce = tblAnnonce.id GROUP BY tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, tblbillede.hovedbillede
Jeg er ikke sikker på det virker, men jeg prøve følgende:
SELECT tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, COUNT(tblBillede.annonce) AS AntalBilleder, first (filnavn) FROM (tblAnnonce INNER JOIN tblType ON tblAnnonce.type = tblType.id) LEFT JOIN tblBillede ON tblBillede.annonce = tblAnnonce.id GROUP BY tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, tblbillede.hovedbillede, tblbillede.id
Jeg får fejlen: "You tried to execute a query that does not include the specified expression 'billede_navn' as part of an aggregate function."
Tilføjer jeg billede_navn til GROUP BY-delen, får jeg fejlen: "No current record."
Fjerner jeg billede_top fra GROUP BY-delen, og lader billede_navn stå, får jeg ingen fejl, men til gengæld får jeg også annonce 1 og 2 vist 2 gange -- én gang for hvert billede der henviser til annoncerne :)
hovsa - jeg fik vist lige brugt de forkerte felt-navne :) i ovenstående - det skulle se således ud:
Jeg får fejlen: "You tried to execute a query that does not include the specified expression 'filnavn' as part of an aggregate function."
Tilføjer jeg filnavn til GROUP BY-delen, får jeg fejlen: "No current record."
Fjerner jeg hovedbillede fra GROUP BY-delen, og lader filnavn stå, får jeg ingen fejl, men til gengæld får jeg også annonce 1 og 2 vist 2 gange -- én gang for hvert billede der henviser til annoncerne :)
SELECT tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, COUNT(tblBillede.annonce) AS AntalBilleder, first (filnavn) FROM (tblAnnonce INNER JOIN tblType ON tblAnnonce.type = tblType.id) LEFT JOIN tblBillede ON tblBillede.annonce = tblAnnonce.id GROUP BY tblAnnonce.id, tblAnnonce.overskrift, tblType.navn ORDER BY tblAnnonce.id, tblbillede.hovedbillede, tblbillede.id
Det ser fornuftigt ud med den ORDER BY-del, men jeg får fejlen: "You tried to execute a query that does not include the specified expression 'tblBillede.hovebillede' as part of an aggregate function."
Tilføjer jeg tblBillede.hovedbillede til GROUP BY-delen, får jeg samme fejl, bare med tblBillede.id. Tilføjer jeg så også tblBillede.id til GROUP BY-delen, får jeg fejlen "No current record".
SELECT tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, Count(qBillede.filnavn) AS AntalBilleder, First(qBillede.filnavn) AS FørsteOffilnavn FROM (tblAnnonce INNER JOIN tblType ON tblAnnonce.type = tblType.id) LEFT JOIN (select * from tblBillede ORDER BY hovedbillede,id) as qbillede ON qBillede.annonce = tblAnnonce.id GROUP BY tblAnnonce.id, tblAnnonce.overskrift, tblType.navn ;
Wow! Det virker NÆSTEN! Det eneste problem er, at den returnerer billede1.jpg til annonce 2, hvor den burde returnere billede2.jpg, da dette er hovedbillede. Jeg har prøvet at skrive "ORDER BY hovedbillede DESC, id" i sub-select'en, men det giver samme resultat.
Hvis du virkelig ingen løsning har på problemet, så bare læg endnu et svar. For du skal da point for anstrengelserne indtil videre. (Jeg afviste egentlig bare det andet svar for at vække din opmærksomhed ;)
Jeg må bare oprette et nyt spørgsmål vedr. den query du kom frem til, for så vidt jeg kan se burde den virke efter hensigten. Tror det er en fejl i Access eller sådan noget lignende :)
Det er lidt af en fusker løsning, med det ser ud til at virke
SELECT tblAnnonce.id, tblAnnonce.overskrift, tblType.navn, antalofid, filnavn FROM (tblAnnonce INNER JOIN tblType ON tblAnnonce.type = tblType.id) LEFT JOIN (SELECT annid, antalofid, filnavn from (SELECT tblAnnonce.id as annid, Min(IIf([hovedbillede],-1,1)*[tblbillede].[id]) AS krit, Count(tblAnnonce.id) AS AntalOfid FROM tblAnnonce INNER JOIN tblBillede ON tblAnnonce.id = tblBillede.annonce GROUP BY tblAnnonce.id) , tblBillede where abs (krit) = tblBillede.id) as qb ON qb.annid = tblAnnonce.id
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.
