[MySQL] Query geeft niet het gewenste resultaat

[VR46]

Hallo,

Ik ben al een paar dagen bezig met het bouwen van een query, maar ik kom er nog steeds niet helemaal uit. Ik heb nooit veel gebruik gemaakt van bijvoorbeeld de JOIN-syntax, misschien begrijp ik die nog niet goed genoeg.
Ik zal even proberen uit te leggen wat de structuur van de tabellen in kwestie is (het zijn voorbeelden / fictieve tabellen, de echte tabellen hebben een ander doel / andere data), en watvoor resultaat de query moet geven.

Er zijn vier tabellen waar de query gebruik van moet maken:

1. TBL_producten
2. TBL_modellen
3. TBL_kleuren
4. TBL_voorraad

Nu wil ik dus dat het resultaat een lijst geeft van alle producten, gesorteerd op naam, en per product een soort 'sublijst' van maximaal 5 modellen van het product (kunnen er tientallen zijn).
Elk model heeft één bepaalde kleur en een voorraadindicatie. De kleur staat in de tabel 'TBL_kleuren' en de voorraad in 'TBL_voorraad'. Elk model kan bij meerdere winkels op voorraad zijn, maar ik wil alleen de gene met de meeste voorraad zien.
De bijbehorende kleur kan worden opgehaald door de kolom 'model_Kleur_Id' uit tabel 'TBL_modellen' gelijk te stellen aan de kolom 'kleur_ID' uit de tabel 'TBL_kleuren'.
De bijbehorende voorraad kan worden opgehaald door de kolom 'voorraad_Model_ID' gelijk te stellen aan de kolom 'model_ID' uit tabel 'TBL_modellen'.

De query die ik tot dusver heb bedacht gaat als volgt:

SQL:

SELECT
p.product_ID,
p.product_Naam,
m.model_Product_ID,
m.model_ID,
m.model_Kleur_ID,
m.model_OpSite,
k.kleur_ID,
k.kleur_Naam,
v.voorraad_Model_ID,
v.voorraad_Winkel

FROM ((TBL_producten AS p 
LEFT JOIN TBL_modellen AS m ON p.product_ID = m.model_Product_ID
LEFT JOIN TBL_kleuren AS k ON m.model_Kleur_ID = k.kleur_ID
LEFT JOIN TBL_voorraad AS v ON m.model_ID = v.voorraad_Model_ID
)) ORDER BY p.product_Naam ASC";

Deze query resulteert in een lijst die redelijk lijkt op wat ik nodig heb, maar is nog niet goed genoeg aangezien het veel te veel data resulteert.
Wat nog niet goed is aan de query:

1. per product worden álle beschikbare modellen weergegeven, terwijl dat er maximaal 5 mogen zijn
2. per model worden álle winkels die deze op voorraad hebben weergegeven, terwijl ik alléén de winkel met de meeste voorraad wil zien.

Deze twee mankementen zijn vitaal voor het functioneren van het script waarmee ik bezig ben, omdat ik anders alles in PHP moet gaan filteren en omdat de recordset anders véél te groot wordt om vlot verwerkt te worden.
Helaas is het me nog niet gelukt om deze twee mankementen in orde te krijgen, ik heb geen flauw idee hoe ik dat voor elkaar kan krijgen. Ik heb al hier en daar een LIMIT erachter geprobeerd te zetten maar dat geeft niet het gewenste effect...

Excuus voor deze belachelijk grote post, ik hoop dat iemand het voorbeeld kan begrijpen, anders probeer ik nog meer tekst en uitleg te geven!

[Janoz]

Je punt 1 wordt erg lastig om in 1 query te vangen. Het grote probleem is dat je niet definieert welke 5 je van allemaal wilt hebben. Zodra je dat wel hebt is het eventueel nog wel op te lossen met een subquery ipv een join.

Je punt 2 is ook redelijk tricky. Daar zul je waarschijnlijk aan de slag moeten gaan met een HAVING deel, maar ik zie nog niet zo snel voor ogen hoe dit te implementeren zou zijn.

[NMe]

Janoz schreef op donderdag 11 juni 2009 @ 13:47:
Je punt 1 wordt erg lastig om in 1 query te vangen. Het grote probleem is dat je niet definieert welke 5 je van allemaal wilt hebben. Zodra je dat wel hebt is het eventueel nog wel op te lossen met een subquery ipv een join.

Dat zou waarschijnlijk een top 5 zijn, op een bepaalde manier gesorteerd. Voor zover ik weet kun je geen LIMIT gebruiken in subqueries, dus dat wordt lastig. Gewoon in PHP filteren, IMO.

Je punt 2 is ook redelijk tricky. Daar zul je waarschijnlijk aan de slag moeten gaan met een HAVING deel, maar ik zie nog niet zo snel voor ogen hoe dit te implementeren zou zijn.

Groupen op productnaam, voorraad, HAVING voorraad = MAX(voorraad) maybe? Geen idee.

[Janoz]

NMe schreef op donderdag 11 juni 2009 @ 13:50:

Groupen op productnaam, voorraad, HAVING voorraad = MAX(voorraad) maybe? Geen idee.

Dan krijg je toch maar 1 product terug met de maximale voorraad en niet de maximale voorraad per product?

[Voutloos]

Vraag 2 is een standaard groupwise maximum

[NMe]

True: http://dev.mysql.com/doc/...mum-column-group-row.html

[RobertMe]

De top 5 zou ik apart ophalen, omdat je anders ook nog eens 5 keer de info van alle producten ophaalt

NMe schreef op donderdag 11 juni 2009 @ 13:50:
[...]

Dat zou waarschijnlijk een top 5 zijn, op een bepaalde manier gesorteerd. Voor zover ik weet kun je geen LIMIT gebruiken in subqueries, dus dat wordt lastig. Gewoon in PHP filteren, IMO.

Uhm, ik zou niet weten waarom je geen LIMIT kunt gebruiken in een subquery. Heb het even in PostgreSQL getest, en geeft me toch gewoon een resultaat. Waar je natuurlijk wel op moet letten is dat je een subquery met meerdere resultaten in de FROM/JOIN gebruikt, en niet ergens waar je maar een record mag krijgen (zoals WHERE, of SELECT)

NMe schreef op donderdag 11 juni 2009 @ 13:50:
[...]

Groupen op productnaam, voorraad, HAVING voorraad = MAX(voorraad) maybe? Geen idee.

Subquery, die sorteren op voorraad, beginnen bij de grootste voorraad en dan LIMIT 1

per product worden álle beschikbare modellen weergegeven, terwijl dat er maximaal 5 mogen zijn

Dan moet je een JOIN op een subquery doen in plaats van een hele tabel. Die subquery geef je dan de LIMIT

per model worden álle winkels die deze op voorraad hebben weergegeven, terwijl ik alléén de winkel met de meeste voorraad wil zien.

Zie m'n reactie op de opmerking van NMe (in combinatie met bovenstaande over top 5)

[VR46]

Janoz schreef op donderdag 11 juni 2009 @ 13:47:
Je punt 1 wordt erg lastig om in 1 query te vangen. Het grote probleem is dat je niet definieert welke 5 je van allemaal wilt hebben. Zodra je dat wel hebt is het eventueel nog wel op te lossen met een subquery ipv een join.

Tja, dat is begrijpelijker met de productietabellen, maar uhm, bij dit voorbeeld maakt het niet echt veel uit, laten we dan zeggen, op firmwareversie ofzo In de productietabel gaat het op datum.

NMe schreef op donderdag 11 juni 2009 @ 13:50:
[...]
Dat zou waarschijnlijk een top 5 zijn, op een bepaalde manier gesorteerd. Voor zover ik weet kun je geen LIMIT gebruiken in subqueries, dus dat wordt lastig. Gewoon in PHP filteren, IMO.

Dat is helaas geen optie, want dan moet ik PHP door een recordset van tienduizenden rows gaan laten worstelen, ordenen, filteren etc. --> rokende server

NMe schreef op donderdag 11 juni 2009 @ 13:50:
[...]

Groupen op productnaam, voorraad, HAVING voorraad = MAX(voorraad) maybe? Geen idee.

Dat is misschien wel een goeie, ja. Eerst heb ik (zonder te veel nadenken) MAX in een subquery geprobeerd maar toen kreeg ik slechts een resultaat van 1 enkele row....

[ Voor 20% gewijzigd door VR46 op 11-06-2009 14:16 ]

[VR46]

RobertMe schreef op donderdag 11 juni 2009 @ 14:00:
De top 5 zou ik apart ophalen, omdat je anders ook nog eens 5 keer de info van alle producten ophaalt


[...]

Uhm, ik zou niet weten waarom je geen LIMIT kunt gebruiken in een subquery. Heb het even in PostgreSQL getest, en geeft me toch gewoon een resultaat. Waar je natuurlijk wel op moet letten is dat je een subquery met meerdere resultaten in de FROM/JOIN gebruikt, en niet ergens waar je maar een record mag krijgen (zoals WHERE, of SELECT)


[...]

Subquery, die sorteren op voorraad, beginnen bij de grootste voorraad en dan LIMIT 1


[...]

Dan moet je een JOIN op een subquery doen in plaats van een hele tabel. Die subquery geef je dan de LIMIT


[...]

Zie m'n reactie op de opmerking van NMe (in combinatie met bovenstaande over top 5)

Ik wil het graag zelf oplossen maar ik heb simpelweg nog niet veel verstand van JOIN-syntax, zou je dus misschien een voorbeeldje kunnen geven aan de hand van mijn voorbeeldquery?

[Janoz]

Het probleem van enkel een max is dat je kwijtraakt om welke winkel het eigenlijk gaat. Het is immers een aggregerende functie. In dat opzicht is de oplossing van RobertMe beter.

[pedorus]

cbernardini schreef op donderdag 11 juni 2009 @ 14:10:
[...]

Ik wil het graag zelf oplossen maar ik heb simpelweg nog niet veel verstand van JOIN-syntax, zou je dus misschien een voorbeeldje kunnen geven aan de hand van mijn voorbeeldquery?

Ik zie eigenlijk ook niet in hoe je probleem 1 gaat oplossen met een subquery met limit 5, dus ik ben benieuwd Als het aantal modellen niet al te groot is (<100), zou ik gewoon filteren in PHP. Anders bijvoorbeeld iets ingewikkelds met COUNT (where (select count(*) from ... as ... where ...)<5). MySQL mist wat handige functies die bijvoorbeeld MSSQL wel heeft.
Probleem 2 heeft Voutloos de oplossing al voor aangedragen (groupwise max).

[NMe]

RobertMe schreef op donderdag 11 juni 2009 @ 14:00:
Uhm, ik zou niet weten waarom je geen LIMIT kunt gebruiken in een subquery. Heb het even in PostgreSQL getest, en geeft me toch gewoon een resultaat.

quote: http://dev.mysql.com/doc/refman/5.1/en/subquery-errors.html

ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = "This version of MySQL does not yet support
'LIMIT & IN/ALL/ANY/SOME subquery'"

http://bugs.mysql.com/bug.php?id=12587

[RobertMe]

pedorus schreef op donderdag 11 juni 2009 @ 14:24:
[...]

Ik zie eigenlijk ook niet in hoe je probleem 1 gaat oplossen met een subquery met limit 5, dus ik ben benieuwd

Het zal wel mogelijk zijn, maar efficient/leesbaar zal het niet worden, ik zou dit zelf ook met PHP doen. Alle producten ophalen en dan de artikelen ophalen per product en daarbij meteen de winkel koppelen.

NMe schreef op donderdag 11 juni 2009 @ 14:28:
[...]


[...]

http://bugs.mysql.com/bug.php?id=12587

MySQL...

[pedorus]

NMe schreef op donderdag 11 juni 2009 @ 14:28:
[...]


[...]

http://bugs.mysql.com/bug.php?id=12587

Hm, bij nader inzien is er dus wel een workaround:

quote: http://bugs.mysql.com/bug.php?id=12587#c176367

I've found another way to use the keyword LIMIT in subqueries that actually works. Just
place your subquery in the FROM of another select and use that one as your subquery, as in
the following example:

SQL:

SELECT * FROM t1 WHERE s1 IN 
(SELECT * FROM (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1) Alias)

Dus dan kun je iets doen als:

SQL:

where m.model_id in 
(select * from 
 (select m2.model_id from modellen as m2 
    where m2.productid=m.productid 
    order by m2.model_id limit 5) alias1)

[VR46]

Oke ik heb een idee gekregen, waarbij ik uitga van de modellen tabel en daarop uit ga wijden.
Nu zit ik alleen met een brandende vraag:

is er een manier waarop je je resultaten kan limiteren tot x rijen per waarde van kolom y? Klinkt misschien heel vaag, in menselijkere taal: 'ik wil maximaal 15 rijen die voor kolom Y dezelfde waarde hebben, dus als er 45 rijen zijn die bij kolom Y waarde X hebben, wil ik slechts de eerste 15 rijen.'

Is dat mogelijk in een enkele query?

[Verwijderd]

cbernardini schreef op maandag 15 juni 2009 @ 12:07:
Oke ik heb een idee gekregen, waarbij ik uitga van de modellen tabel en daarop uit ga wijden.
Nu zit ik alleen met een brandende vraag:

is er een manier waarop je je resultaten kan limiteren tot x rijen per waarde van kolom y? Klinkt misschien heel vaag, in menselijkere taal: 'ik wil maximaal 15 rijen die voor kolom Y dezelfde waarde hebben, dus als er 45 rijen zijn die bij kolom Y waarde X hebben, wil ik slechts de eerste 15 rijen.'

Is dat mogelijk in een enkele query?

Zonder naar de rest van je post gekeken te hebben. Dit kan (althans in MSSQL) met:

SQL:

SELECT TOP n ... FROM .... WHERE kolom Y = waarde X 

Met n=aantal te tonen rijen.

[NMe]

Verwijderd schreef op maandag 15 juni 2009 @ 13:11:
[...]


Zonder naar de rest van je post gekeken te hebben. Dit kan (althans in MSSQL) met:

SQL:

SELECT TOP n ... FROM .... WHERE kolom Y = waarde X 

Met n=aantal te tonen rijen.

Dat beantwoordt zijn vraag niet; hij wil n rijen terug per mogelijke verschillende waarde van die ene kolom. En volgens mij is die vraag hierboven al beantwoord?

[VR46]

NMe schreef op maandag 15 juni 2009 @ 13:19:
[...]

Dat beantwoordt zijn vraag niet; hij wil n rijen terug per mogelijke verschillende waarde van die ene kolom. En volgens mij is die vraag hierboven al beantwoord?

Sorry ik denk dat je helemaal gelijk hebt