[SQL] Count ism left join geeft geen resultaten

[zeroxcool]

Hoi iedereen, ik zit al een aantal dagen met het volgende probleem:

Ik wil zeg maar een gebruikers tabel dynamisch uit kunnen breiden, dit wilde ik doen aan de hand van labels zeg maar. Hiervoor heb ik de volgende tabellen gebruikt (enkel relevante items heb ik vermeld):

label
id
name

label_value
id
memberid
labelid
value

Hier wat voorbeeld records:

label
id	name
1	Kleur ogen
2	Favoriete voetbalclub
3	Beste forum
4	Gewicht

label_value
id	memberid	labelid	value
1	12	1	Blauw
2	64	1	Bruin
3	96	1	Groen
4	42	2	PSV
5	534	2	PSV

Ik gebruik MySQL 5.1 als RDBMS. Wat ik nu wil bereiken is een query die alle labels weergeeft (select * from label; bij wijze van spreke) met daarbij het aantal values zeg maar. Nu had ik daarvoor deze query geschreven:

SQL:

select l.id, l.name, count(v.memberid) 
from label l 
left join label_value v on 
  l.id = v.labelid 
group by v.labelid;

En dan eindelijk het probleem . Deze query geeft kortweg dit resultaat terug:

id	name	count(v.memberid)
3	Beste forum	0
1	Kleur ogen	3
2	Favoriete voetbalclub	2

Dit resultaat klopt gewoon, kijk maar naar de label_value tabel. Echter! Waar is het vierde label gebleven? Dit probleem doet zich dus ook voor als ik zeg maar de hele label_value tabel leeg maak, dan zie ik alleen het - voor de database - eerste label als resultaat met een count van 0. Daarna komt er dus geen resultaat meer, dit zou dus wel moeten aangezien er meerdere label tupels zijn.

Wat ik al heb geprobeerd: ipv een left join een inner join (maar ook dit wil je niet, als er geen 'resultaten' zijn dan wil je het label item wel meenemen). En een heleboel varianten op de group waarde, maar niets mocht baten... Help .

[Robbemans]

Een group by werkt altijd op de velden in de select. Je kunt je group by dus enkel als

SQL:

group by
  l.id, l.name 

gebruiken.

Dat je uberhaupt een resultaat krijgt is mooi meegenomen.


Je zou je count als subselect kunnen opnemen. Dat moet met MySQL 5 kunnen.
Ik ken MySQL 5 niet goed genoeg om te kunnen bepalen of je het met boolean aggregates kunt doen, hetgeen een stuk sneller is dan subselects.

[ Voor 53% gewijzigd door Robbemans op 28-02-2006 10:01 ]

[zeroxcool]

Robbemans schreef op dinsdag 28 februari 2006 @ 09:59:
Een group by werkt altijd op de velden in de select. Je kunt je group by dus enkel als

SQL:

group by
  l.id, l.name 

gebruiken.

Dat je uberhaupt een resultaat krijgt is mooi meegenomen.

Ohhh, erg is dat toch hè, dan zit je uren te turen en te doen, terwijl de oplossing gewoon voor de hand ligt . Het werkt nu inderdaad, ik dank je zeer hartelijk voor je snelle reactie.

Even iets om de pijn te verzachten, pikt een 'echt' RDBMS zoals Oracle zo'n query eigenlijk? Of is het in principe een correcte query?

[Dido]

Met hierboven.
Je lijkt erg ingewikkeld te denken

SQL:

select l.id, l.name, count(*) 
from label l 
left join label_value v 
   on l.id = v.labelid 
group by l.id;

Zou het gewoon moeten doen?

[zeroxcool]

Dido schreef op dinsdag 28 februari 2006 @ 10:03:
Met hierboven.
Je lijkt erg ingewikkeld te denken

SQL:

select l.id, l.name, count(*) 
from label l 
left join label_value v 
   on l.id = v.labelid 
group by l.id;

Zou het gewoon moeten doen?

Jij te makkelijk . Nee, ook dit had ik geprobeerd, nu krijg je echter voor iedere query minimaal één resultaat meer (omdat ieder label item ook meetelt). In principe niet erg, dan haal je natuurlijk gewoon overal van ieder antwoord er één af. Maar een echte nette oplossing is het niet .

[Robbemans]

ZeRoXcOoL schreef op dinsdag 28 februari 2006 @ 10:02:
[...]

Ohhh, erg is dat toch hè, dan zit je uren te turen en te doen, terwijl de oplossing gewoon voor de hand ligt . Het werkt nu inderdaad, ik dank je zeer hartelijk voor je snelle reactie.

Even iets om de pijn te verzachten, pikt een 'echt' RDBMS zoals Oracle zo'n query eigenlijk? Of is het in principe een correcte query?

Ach ja... Ik ken dat.

Even aannemende dat je MSSQL Server een 'echte' RDBMS vind, dan pikt die dit niet. Je krijgt een foutmelding over het feit dat de te grouperen kolommen in de select moeten zitten. Bij Oracle weet ik dit niet.

[JKVA]

Het probleem zit in de GROUP BY. Je hebt daar l.name niet gezet, terwijl dit eigenlijk wel moet. Het lijkt erop dat MySQL niet zo strikt volgens de standaarden werkt op dit gebied, want die geeft foutmeldingen dat je niet alle niet-aggregates gegroepeerd hebt. (kromme zin, maar het idee erachter klopt wel)

Maak je GROUP BY maar zo: GROUP BY l.id, l.name

Ps. Zet er ook maar een ORDER BY l.name onder. Dan is je lijstje mooi altijd gesorteerd en het scheelt ook snelheid, omdat de indexen dan beter gebruikt worden.

Ps2. Waarom die aliasen voor je tabellen? Het scheelt 10 karakters ofzo, maar gaat ten koste van de leesbaarheid naar mijn idee.

edit:
Blijkbaar type ik nogal traag

[ Voor 4% gewijzigd door JKVA op 28-02-2006 10:10 ]

[JKVA]

Ook andere open source DBMS-en doen hier weleens moeilijk over, PostGreSQL bijvoorbeeld. Die hanteert zeer strikt de ANSI-SQL norm en is daardoor vrij handig als leermiddel.

[zeroxcool]

JKVA schreef op dinsdag 28 februari 2006 @ 10:08:
Het probleem zit in de GROUP BY. Je hebt daar l.name niet gezet, terwijl dit eigenlijk wel moet. Het lijkt erop dat MySQL niet zo strikt volgens de standaarden werkt op dit gebied, want die geeft foutmeldingen dat je niet alle niet-aggregates gegroepeerd hebt. (kromme zin, maar het idee erachter klopt wel)

Maak je GROUP BY maar zo: GROUP BY l.id, l.name

Ps. Zet er ook maar een ORDER BY l.name onder. Dan is je lijstje mooi altijd gesorteerd en het scheelt ook snelheid, omdat de indexen dan beter gebruikt worden.

Ps2. Waarom die aliasen voor je tabellen? Het scheelt 10 karakters ofzo, maar gaat ten koste van de leesbaarheid naar mijn idee.

edit:
Blijkbaar type ik nogal traag

Die order zit in de normale query ook, maar ik heb alle niet relevante informatie uit dit topic gesloopt, er zitten natuurlijk veel meer items in deze tabellen, vandaar.

Die aliassen vind ik persoonlijk wel duidelijk, maar dat is dus ook puur ieder voor zich natuurlijk .

Misschien dat ik binnenkort voor me eens ga verdiepen in Postgre. En dan nog snel een offtopic vraag: kan Postgre ook meerdere aparte indexen in één query gebruiken, dat mis ik namelijk een beetje in MySQL?

Iedereen in ieder geval hartelijk dank voor je reacties + tijd!

[JKVA]

ZeRoXcOoL schreef op dinsdag 28 februari 2006 @ 10:37:
[...]
Misschien dat ik binnenkort voor me eens ga verdiepen in Postgre. En dan nog snel een offtopic vraag: kan Postgre ook meerdere aparte indexen in één query gebruiken, dat mis ik namelijk een beetje in MySQL?

Ja, maar MySQL toch ook? Plus nog allemaal coole dingen zoals allemaal coole queries bouwen en zo, transacties, etc.

[P_de_B]

MySQL gebruikt per tabel maar 1 index.

[Knutselsmurf]

Hoewel MySQL idd maar 1 index per tabel gebruikt, kun je er natuurlijk wel meerdere aanmaken. MySQL zoekt dan zelf uit welke van de gedefinieerde indices bruikbaar is. Let er daarbij wel op, dat een index op (a,b,c) ook tegelijk een index is op (a,b) en op (a).

[zeroxcool]

Knutselsmurf schreef op dinsdag 28 februari 2006 @ 10:45:
Hoewel MySQL idd maar 1 index per tabel gebruikt, kun je er natuurlijk wel meerdere aanmaken. MySQL zoekt dan zelf uit welke van de gedefinieerde indices bruikbaar is. Let er daarbij wel op, dat een index op (a,b,c) ook tegelijk een index is op (a,b) en op (a).

Dat klopt, maar als je dynamisch mensen selecties laat maken, op bijvoorbeeld eerst c dan op a, dan werkt de index dus niet meer. En om nou op iedere mogelijke combinatie een index te zetten . Nu heb ik gewoon op de items met de grootste kardinaliteit een enkele index gezet. Zo wordt de eerste selectie in ieder geval goed verdund met behulp van de index en kan daarna via een lineaire zoekactie de rest worden uitgesloten, maar als MySQL bijvoorbeeld iedere apart gezette index 'on-the-fly' kan combineren is dat natuurlijk ideaal.

[JKVA]

Bij zulke dingen is het gewoon een kwestie van uitproberen. Welke overzichten en welke selecties worden vaak gedraaid en hoe belangrijk is het dat die snel gaan? Indien er een belangrijk overzicht te traag blijkt, gooi je er een extra index op en kijkt of het wel acceptabel is.

Alle combinaties kun je niet afvangen, maar dat moet je ook niet willen, want dan wordt invoegen een probleem.

edit:
Het is overigens anders bij indexen waarmee je uniqueness wilt afdwingen ofzo, die moeten er gewoon op liggen, want die bepalen de juistheid van de gegevens

[ Voor 19% gewijzigd door JKVA op 28-02-2006 12:39 ]

[curry684]

ZeRoXcOoL schreef op dinsdag 28 februari 2006 @ 10:02:
[...]

Even iets om de pijn te verzachten, pikt een 'echt' RDBMS zoals Oracle zo'n query eigenlijk? Of is het in principe een correcte query?

Nee het is onzin.

[zeroxcool]

curry684 schreef op dinsdag 28 februari 2006 @ 12:50:
[...]

Nee het is onzin.

Die FAQ heb ik een tijd geleden ook eens doorgelezen en heb dat toen wel onthouden. Vandaar ook die vraag of het überhaupt antwoord geven wel correct is.

Rest nog het antwoord op de meerdere losse indexen vraag ism Postgre .

[JKVA]

Het antwoord daarop is dus ja. Je kunt zo veel indexen aanmaken als je wilt, maar per query per tabel wordt er maar eentje gebruikt. Die ene mag eventueel wel uit meerdere kolommen of functies ofzo bestaan.

http://www.postgresql.org...indexes-bitmap-scans.html

edit:

Ik lees overigens nu in dat artikel dat postgres 8.1+ meerdere indexen op een tabel kan gebruiken, tenminste dat maak ik er uit op.

"Beginning in release 8.1, PostgreSQL has the ability to combine multiple indexes (including multiple uses of the same index) to handle cases that cannot be implemented by single index scans."

[ Voor 39% gewijzigd door JKVA op 01-03-2006 13:08 ]

[ACM]

JKVA schreef op woensdag 01 maart 2006 @ 12:59:
Ik lees overigens nu in dat artikel dat postgres 8.1+ meerdere indexen op een tabel kan gebruiken, tenminste dat maak ik er uit op.

"Beginning in release 8.1, PostgreSQL has the ability to combine multiple indexes (including multiple uses of the same index) to handle cases that cannot be implemented by single index scans."

Klopt, mysql kan het in 5.0 ook een beetje, meen ik. Maar de methode van Postgres is voor zover ik heb kunnen nagaan een stuk uitgebreider en hoe mysql het nu kan, kon eerder ook al deels in postgres.