Verkeerd aantal gegevens via INNER JOIN icm SELECT(SELECT

[Verwijderd]

Met de onderstaande code moet er uit de database alle combinaties van een bepaald merk/model gsm met een nieuw abonnement getoond worden. Samengevoegd per 'abonnement met contractsduur', met de goedkoopste Totprijs in de lijst. Dit moeten er 157 zijn maar ik krijg er maar 32.

code:

SELECT abo_titel, abo_minuten, abo_prijs, abo_aansluitkosten, url, Prijs, merk, model, provider, abonnement, duur, verzendkosten, 
Prijs+verzendkosten+abo_aansluitkosten AS Totprijs

FROM data  d1 INNER JOIN abonnementen a1
ON
abo_provshort = provider AND
abo_short = abonnement AND
abo_verlenging = vernieuwing AND
abo_duur = duur 
WHERE 
merk ='". $result['prod_short_merk'] ."' AND 
model ='". $result['prod_short_model'] ."' AND 
vernieuwing =''  (leeg is nieuw abonnement)
AND
Prijs+verzendkosten+abo_aansluitkosten = (SELECT
MIN(d2.Prijs+d2.verzendkosten+a2.abo_aansluitkosten) AS Totprijs
FROM data d2 INNER JOIN abonnementen a2 ON
a2.abo_provshort = d2.provider AND
a2.abo_short = d2.abonnement AND d2.vernieuwing = a2.abo_verlenging AND abo_duur = duur 
)
GROUP BY abonnement,duur
ORDER BY Totprijs ASC, abo_prijs ASC

De tabellen data en abonnementen kunnen gekoppeld worden op de onderstaande manier:
provider = abo_provshort
abonnement = abo_short
duur = abo_duur
vernieuwing = abo_verlenging

$result['prod_short_merk'] en $result['prod_short_model'] worden via GET verkregen.

[ Voor 3% gewijzigd door Verwijderd op 11-07-2009 00:08 ]

[_js_]

Ik begrijp niet precies wat je in je tabellen hebt staan (data zijn alle toestel+abbonement combinaties? en abonnementen alleen informatie over abonnementen?) Als je beter uitlegt wat voor tabellen je hebt kunnen mensen je beter helpen.

Wat je nu doet (in de subquery) is het goedkoopste toestel zoeken voor een bepaalde duur. Alleen toestellen die dezelfde prijs hebben laat je toe in het resultaat, alle toestellen die duurder zijn worden dus niet weergegeven.

Let ook op of je group by wel voldoende velden bevat, en let op sql injectie.

[Verwijderd]

In de tabel data staat informatie over het toestel icm een abonnement/verlenging.
voorbeeld:

items_id - 123
url - http://website
Prijs - 232.55
merk - htc
model - s740-zwart
provider - t-mobile
abonnement - relax-200
duur - 12 (kan 12,18 of 24 zijn)
vernieuwing - (kan '' of '1' zijn, '' = nieuw '1' = verlenging)
verzendkosten - 9.95

In de tabel abonnementen staan alle gegevens over de abonnementen.
voorbeeld:
abo_id - 23
abo_titel - T-Mobile Relax 100
abo_provider - T-Mobile Relax
abo_abonnement - 100
abo_duur - 12 (kan 12,18 of 24 zijn)
abo_verlenging - (kan '' of '1' zijn, '' = nieuw '1' = verlenging)
abo_minuten - 200
abo_prijs - 24.5
abo_aansluitkosten - 0
abo_provshort - t-mobile
abo_short - relax-200

[ Voor 52% gewijzigd door Verwijderd op 11-07-2009 13:31 ]

[Verwijderd]

Met die subquery wil ik per abonnementsvorm (provider + abonnement + duur + vernieuwing('' of '1')) de voordeligste keuze laten tonen.
voorbeeld van alle T-mobile Relax 200:

T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 132,55 - Totprijs: 157,05 - Winkel C
T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 32,55 - Totprijs: 32,55 - Winkel B
T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 232,55 - Totprijs: 257,05 - Winkel A
van de combinatie T-Mobile Relax 200 12 maanden zou hij alleen de gegevens van Winkel B moeten tonen, omdat deze de voordeligste is.

[Glorificationer426]

Ik wil niet zeikerig overkomen, maar eerlijk gezegd vind ik het een abominabele tabelstructuur en als je de mogelijkheid/tijd heb deze te herzien zou ik het zeker overwegen. Wat rare dingen die mij zo opvallen:

• Tabelnaamgeving. Tabelnamen zoals data zijn nogal ambigu. Staat er in de andere tabellen geen data dan? Er staan toch telefoons in, noem het dan ook telefoons of gsms oid.
• Inconsistente kolomnamen. je prefixed alle kolommen in de abonnementen tabel met abo. Dit vind ik nogal lelijk en dubbelop, je weet immers toch uit welke tabel je selecteert als je de tabelnaam prefixed? Maar vervolgens gebruik je in andere tabellen helemaal geen prefix.
• Dubbele dataopslag. Hier is vast een hele mooie term voor in SQL, maar dat schiet me even niet te binnen Je slaat in ieder geval o.a. abonnementsduur en abonnementsnamen in beide tabellen op. Dit kan tot nogal rare onconsistenties in je dataset lijden als je bijvoorbeeld een abonnement update en je vergeet de data tabel te updaten. Waarom niet gewoon een abonnement_id opslaan als reference/foreign key in de data tabel naar de abonnementen tabel? Is ook nog eens een behoorlijk stuk sneller met joins en je krijgt er veel leesbaardere queries van.

Verder zou ik de verlenging als simpele boolean of voor mijn part als bit opslaan in plaats van wat het nu is, geeft toch net iets meer betekenis aan de waarde, maar dat is misschien mierenneukerij.

Maar genoeg gezeik over de structuur want ik was al bijna vergeten dat het eigenlijk over de query ging
Eerlijk gezegd vind ik de huidige query nogal raar en vooral waarom je de hele subquery gebruikt, volgens mij is die namelijk helemaal niet nodig. Wat ik begrijp van de uitleg is dat je voor de geselecteerde gsm en abonnement (de gejoinde 'view' dus) de goedkoopste totaalprijs wilt. Dit kan toch makkelijk met een simpele MIN(totaalprijs) query gegroepeerd naar abonnement?

SQL:

SELECT abo.titel, abo.prijs, abo.aansluitkosten, 
       gsm.url, gsm.prijs, gsm.merk, 
       MIN(gsm.prijs+abo.verzendkosten+abo.aansluitkosten) AS totaalprijs
FROM abonnementen AS abo INNER JOIN gsms AS gsm
ON gsm.abo_id = abo.abo_id
WHERE gsm.merk ='". $result['prod_short_merk'] ."' AND 
      gsm.model ='". $result['prod_short_model'] ."' AND 
      abo.vernieuwing = 0
GROUP BY abo.abonnement, abo.duur
ORDER BY totaalprijs ASC, gsm.prijs ASC

Zelf niet getest, maar voor zover ik het begrijp zou het zoiets moeten worden.

[Verwijderd]

Bedankt Glorificationer426 voor je info. Dit is voor het eerst dat ik een wat uitgebreide database heb en ben eigenlijk verkeerd begonnen met de benamingen.

De SQL die je gaf geeft wel de laagste totaalprijs weer, maar de gegevens zoals Toestelprijs en de Winkel/url horen daar weer niet bij.

Dit zijn de gegevens die uit de database gehaald worden:
T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 132,55 - Totprijs: 157,05 - Winkel C
T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 32,55 - Totprijs: 32,55 - Winkel B
T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 232,55 - Totprijs: 257,05 - Winkel A

en dit geeft hij dan weer:
T-Mobile Relax 200 - 12 mnd - 200 min - Prijs abo: 24,50 - Toestelprijs: 132,55 - Totprijs: 32,55 - Winkel C

[Glorificationer426]

Hmm ja zo werken aggregate operators. De SQL standaard zegt volgens mij ook niets over welke waarde hij moet teruggeven voor kolommen in de select clause die niet voorkomen in de group by. Wat voor dbms gebruik je eigenlijk? MySQL?

Als je ook nog de prijs voor de mobiel apart wilt zal je het denk ik weer met een subquery moeten doen:

SQL:

SELECT abo_titel, abo_minuten, abo_prijs, abo_aansluitkosten, url, Prijs, merk, model, provider, abonnement, duur, verzendkosten, 
Prijs+verzendkosten+abo_aansluitkosten AS Totprijs
FROM data  d1 INNER JOIN abonnementen a1
ON abo_provshort = provider AND
   abo_short = abonnement AND
   abo_verlenging = vernieuwing AND
   abo_duur = duur 
WHERE merk ='". $result['prod_short_merk'] ."' AND 
      model ='". $result['prod_short_model'] ."' AND 
      vernieuwing =''
AND Prijs+verzendkosten+abo_aansluitkosten = (
    SELECT MIN(d2.Prijs+d2.verzendkosten+a2.abo_aansluitkosten) AS mintotprijs
    FROM data d2 INNER JOIN abonnementen a2 ON
         a2.abo_provshort = d2.provider AND
         a2.abo_short = d2.abonnement AND d2.vernieuwing = a2.abo_verlenging AND abo_duur = duur
    WHERE 
         d2.merk ='". $result['prod_short_merk'] ."' AND 
         d2.model ='". $result['prod_short_model'] ."' AND
         d2.vernieuwing ='' AND
         a2.abonnement = a1.abonnement AND
         a2.duur = a1.duur
)
GROUP BY a1.abo_abonnement, a1.abo_duur
ORDER BY Totprijs ASC, abo_prijs ASC

In de subquery heb je natuurlijk wel wat meer checks nodig dat je ook het minimum selecteert van het mobieltje/abonnement dat geselecteerd is in de originele query in plaats van alleen het globale minimum in je startpost (zoals _js_ al aangaf). Ik _denk_ dat het zoiets moet worden, maar ik heb natuurlijk niks getest. Het gaat meer om het idee.

[ Voor 3% gewijzigd door Glorificationer426 op 11-07-2009 18:55 ]

[Verwijderd]

Glorificationer426, mag ik je hartelijk danken?

Het werkt :-)

Ik heb alleen het onderstaande stukje hoeven aan te passen en toen werkte het als een speer. Ik ga alleen even zoals de boel een beetje netjes maken, waar _js_ het over had (natuurlijk ook bedankt;-) ).

code:

a2.abonnement = a1.abonnement AND
a2.duur = a1.duur

in

code:

d2.abonnement = d1.abonnement AND
d2.duur = d1.duur