[mysql] Probleem met opstellen query

[mahi]

Hoi mensen,

Ik heb 2 MySQL tables die gerelateerd zijn aan elkaar en waaruit ik gegevens wil weergeven. Om het probleem overzichtelijk weer te geven, geef ik hier sterk vereenvoudigde versies van de tables. De tabellen en queries bevatten dus veel meer kolommen en rijen dan hier aangegeven! Reduceren tot 1 tabel is dus niet mogelijk!

tabel usergroups:

code:

| usergroup_id | usergroup_name |
+--------------+----------------+
|            1 | Group1         |
|            2 | Group2         |
|            3 | Group3         |
|            4 | Group4         |

tabel users:

code:

| user_id | user_name | usergroup_id |
+---------+-----------+--------------+
|       1 | Name1     |            3 |
|       2 | Name2     |            1 |
|       3 | Name3     |            1 |
|       4 | Name4     |            2 |

De usergroup_id in de users tabel is gerelateerd met die in de usergroups tabel. Op deze wijze zijn users toegewezen aan een groep. Er kunnen geen users voorkomen die niet behoren tot een usergroup, maar er kunnen wel usergroups zijn die geen users bevatten.

De bedoeling? Een tabel weergeven met als kolommen de groepsnaam (usergroups.usergroup_name), en het aantal users in die groep. Alle groepen moeten worden weergegeven.

code:

SELECT
    usergroups.usergroup_name,
    COUNT(users.user_id) AS num_users
FROM usergroups, users
WHERE usergroups.usergroup_id = users.user_group_id
GROUP BY usergroups.usergroup_id

Wat geeft:

code:

| usergroup_name | num_users |
+----------------+-----------+
| Group1         |         2 |
| Group2         |         1 |
| Group3         |         1 |

Maar... Nu is Group4 achterwege gelaten (omdat die niet voorkomt in de users tabel). De bedoeling is echter dat groepen zonder users ook worden weergegeven (met als num_users 0 natuurlijk). Het resultaat zou er dus als volgt moeten uitzien:

code:

| usergroup_name | num_users |
+----------------+-----------+
| Group1         |         2 |
| Group2         |         1 |
| Group3         |         1 |
| Group4         |         0 |

Ik raak er echter niet uit hoe ik dat met één query-opdracht voor elkaar krijg (als het al mogelijk is). Ik zou natuurlijk met een eerste query alle groepen kunnen opvragen en dan in een while-lus per groep het aantal users in de groep opvragen met een tweede query. Maar dan stuit ik op een ander probleem: Het is noodzakelijk dat er op elke kolom gesorteerd kan worden. Afhankelijk van de door de gebruiker gekozen instelling staat er dus nog

ORDER BY usergroup.usergroup_name ASC LIMIT $offset, 50

of

ORDER BY num_users DESC, usergroup.usergroup_name ASC LIMIT $offset, 50

achteraan de query (in het werkelijke ontwerp zijn het natuurlijk talrijke kolommen waarop gesorteerd moet kunnen worden). Wanneer ik met de 2 queries werk dan moet ik eigenlijk al per sorteermogelijkheid totaal verschillende queries gebruiken. Omdat de table gezien het grote aantal rijen over meerdere pagina's gespreid is (door de "LIMIT $offset, 50") wordt dit vrij complex. Veel extra code dus voor iets dat me relatief eenvoudig lijkt... Daarom dat ik het graag in 1 query wil houden.

Iemand die een helpende hand kan bieden in het opstellen van de juiste query of met zekerheid kan stellen dat het niet mogelijk is met 1 query?

Indien het gevraagde niet mogelijk is met 1 query, dan zal ik maar een kolom num_users toevoegen aan de tabel usergroups. Dat lijkt me alleszins verstandiger dan complexe en potentieel meer foutgevoelige code te schrijven...

[ripexx]

Je moet dan werken met JOINS, je query zal dan iets worden van:

SQL:

SELECT
    usergroups.usergroup_name,
    COUNT(users.user_id) AS num_users
FROM usergroups
    LEFT JOIN users
        ON usergroups.usergroup_id = users.usergroup_id
GROUP BY usergroups.usergroup_id
ORDER BY usergroup_name

Door de where van jouw sluit je namelijk de lege groupen uit

[mahi]

Ongelooflijk, en het werkt nog ook

Keihard bedankt!

Ik moet zeggen dat de uitleg over joins erg summier is in Paul DuBois' boek "MySQL" (welk toch aanzien wordt als het referentiewerk als het op MySQL aankomt)... Dit had ik nooit gevonden uit de uitleg van het boek.

[ Voor 15% gewijzigd door mahi op 14-10-2003 02:16 ]

[ripexx]

Zie ook het SQL gedeelte hier in de FAQ van P&W.

Nadeel van het manual van MySQL is dat deze uit een aantal zeer grote hoofdstukken bestaat en slecht te doorzoeken is op keywords. Dit in tegenstelling tot bijvoorbeeld het manual van PHP.

[mahi]

Ik weet dat het een oud topic is, maar ik heb een aanvullende vraag over de opgave...

ripexx' oplossing werkt uitstekend bij 2 tabellen, maar ondertussen is m'n ontwerp wat uitgebreid zodat ik met 3 tabellen zit. Naast de tabellen usergroups en users uit de originele vraagstelling is er nu een tabel user_privileges bijgekomen.

tabel user_privileges:

code:

| privilege_id | usergroup_id |
+--------------+--------------+
|            1 |            3 |
|            2 |            3 |
|            3 |            4 |
|            4 |            3 |

Er zitten natuurlijk nog andere kolommen in deze tabel zodat de gegevens hierin niet eenvoudig in de tabel usergroups opgenomen kunnen worden.

De vraagstelling is zoals bij de voorgaande opgave. De inhoud van tabel usergroups moet rij voor rij getoond worden, en voor zowel de tabel users als user_privileges moet het aantal maal dat de betreffende usergroup_id van de rij voorkomt geteld worden (COUNT). Het resultaat moet er dus voor bovenstaande voorbeeld tabellen uitzien als:

Resultaat

code:

| usergroup_name | num_users | num_privileges |
+----------------+-----------+----------------+
| Group1         |         2 |              0 |
| Group2         |         1 |              0 |
| Group3         |         1 |              3 |
| Group4         |         0 |              1 |

De code om te tellen is natuurlijk gelijk aan die uit de voorgaande opgave. Alleen komt er nu een COUNT bij:

code:

COUNT(users.user_id) AS num_users,
COUNT(user_privileges.privilege_id) AS num_privileges

Verder gaande op ripexx' voorbeeld dacht ik aan een dubbele LEFT JOIN:

code:

SELECT
    usergroups.usergroup_name,
    COUNT(users.user_id) AS num_users,
    COUNT(user_privileges.privilege_id) AS num_privileges
FROM usergroups
    LEFT JOIN users
        ON usergroups.usergroup_id = users.usergroup_id
    LEFT JOIN user_privileges
        ON usergroups.usergroup_id = user_privileges.usergroup_id
GROUP BY usergroups.usergroup_id;

Maar blijkbaar kun je LEFT JOINs zo niet combineren tot wat ik er mee wil doen (het werkt wel, maar geeft niet wat ik wil). Ik denk dat de oorzaak is dat de tweede LEFT JOIN toegepast wordt op het resultaat van de eerste LEFT JOIN. Dat is natuurlijk niet de bedoeling. De tweede LEFT JOIN moet op tabel usergroups toegepast worden, onafhankelijk van de eerste LEFT JOIN. Ik heb er echter geen idee van hoe je dat noteert in SQL. Ik heb ook geëxperimenteerd met 2 tabellen te joinen met LEFT JOIN tabel1, tabel2, maar dat was ook niet bepaald wat ik zoek.

De PW SQL-FAQ brengt geen uitkomst. Het boek MySQL van Paul DuBois heb ik al opzij gelegd want JOINS beslaan hier amper enkele bladzijden in waarbij nooit meer dan 2 tabellen gebruikt worden. De MySQL website geeft in de commentaren bij LEFT JOIN wel voorbeelden van joins met meerdere tabellen, maar die doen eveneens niet wat ik nodig heb.

Ik hang wel vast aan MySQL 4.0.16, dus subqueries en dergelijke zijn niet bruikbaar.

[ Voor 1% gewijzigd door mahi op 15-01-2004 19:02 . Reden: foutje verholpen ]

[BrZ]

Die query die jij geeft werkt bij mij gewoon, als ik tenminste de tabel privileges een andere naam geeft, "privileges" vind MySQL niet zo leuk

[Verwijderd]

Ik heb niet alles gelezen, maar ik zie dat je problemen hebt met het combineren van joins en hun volgorde, misschien kan dit je dan helpen: [rml][ SQL] JOIN na LEFT JOIN[/rml]

Kort gezegd: MySQL ==

[ Voor 7% gewijzigd door Verwijderd op 15-01-2004 19:02 ]

[mahi]

Sorry, daar had ik niet op gelet. Ik heb privileges in bovenstaand voorbeeld snel naar user_privileges veranderd. In m'n eigenlijke code is dat alleszins niet het probleem omdat de tabelnamen daar nog een voorvoegsel hebben. De query werkt inderdaad, maar toch doet ie niet wat ie zou moeten doen.

code:

SELECT
    usergroups.usergroup_name,
    COUNT(users.user_id) AS num_users,
    COUNT(user_privileges.privilege_id) AS num_privileges
FROM usergroups
    LEFT JOIN users
        ON usergroups.usergroup_id = users.usergroup_id
    LEFT JOIN user_privileges
        ON usergroups.usergroup_id = user_privileges.usergroup_id
GROUP BY usergroups.usergroup_id;

geeft:

code:

| usergroup_name | num_users | num_privileges |
+----------------+-----------+----------------+
| Group1         |         2 |              0 |
| Group2         |         1 |              0 |
| Group3         |         3 |              3 |
| Group4         |         0 |              1 |

Terwijl het zou moeten zijn:

code:

| usergroup_name | num_users | num_privileges |
+----------------+-----------+----------------+
| Group1         |         2 |              0 |
| Group2         |         1 |              0 |
| Group3         |         1 |              3 |
| Group4         |         0 |              1 |

Zoals je kan zien klopt num_users niet in de uitkomst van mijn query. Voor 'Group3' zegt ie dat er 3 users in zitten terwijl je in tabel users duidelijk kunt zien dat er maar 1 user een user_id = 3 heeft. Num_users moest daar dus 1 zijn.

DiEana: Ik zal je topic zometeen eens doornemen, maar ik ben er ondertussen ook al achter dat MySQL niet bepaald 'je van het' is. Helaas ben ik gebonden aan die database door m'n provider

[ Voor 7% gewijzigd door mahi op 15-01-2004 19:14 ]

[Verwijderd]

mahi schreef op 15 januari 2004 @ 19:10:
De query werkt inderdaad, maar toch doet ie niet wat ie zou moeten doen.

De query doet perfect "wat ie zou moeten doen". SQL is een taal waarvan de syntax keurig gedefinieerd is (diverse versies en smaken bestaan ervan, maar ook die zijn keurig gedefinieerd). Als je kijkt wat de betekenis van de commando's, etc. is, dan werkt de query keurig volgens de specs.

Ik denk dat je bedoelt dat de query niet doet wat jij ervan verwacht, maar dat komt eerder doordat jouw verwachtingen niet met de realiteit overeenkomen (je kunt het ook lezen als: doordat je kennis van SQL nog niet voldoende groot is; maar dat klinkt weer zo hard )

Twee joins achter elkaar betekenen in SQL altijd iets als:
(a join b) join c
Als je in de documentatie kijkt heeft een join altijd een linker en een rechter tabel. SQL beschouwt in het geval van a join b join c dat de linker tabel van de tweede join het resultaat van de eerste join is.

code:

SELECT
    usergroups.usergroup_name,
    COUNT(users.user_id) AS num_users,
    COUNT(user_privileges.privilege_id) AS num_privileges
FROM usergroups
    LEFT JOIN users
        ON usergroups.usergroup_id = users.usergroup_id
    LEFT JOIN user_privileges
        ON usergroups.usergroup_id = user_privileges.usergroup_id
GROUP BY usergroups.usergroup_id;

geeft:

code:

| usergroup_name | num_users | num_privileges |
+----------------+-----------+----------------+
| Group1         |         2 |              0 |
| Group2         |         1 |              0 |
| Group3         |         3 |              3 |
| Group4         |         0 |              1 |

Dit klopt perfect volgens de definitie van SQL! Door de twee join wordt voor elk record uit het resultaat van de eerste join een combinatie met de privileges tabel gevonden. Daardoor wordt de count van de user_id automatisch ook 3...
Wat jij wilt zijn de resultaten van twee losse vragen combineren. Je kunt dit doen door de resultaten van twee query's in de taal die je eromheen gebruikt te combineren.

maar ik ben er ondertussen ook al achter dat MySQL niet bepaald 'je van het' is.

Ik weet niet waar je deze wijsheid vandaan hebt?
Elk database systeem heeft z'n voor en z'n nadelen, maar MySQL is bepaald niet een van de slechtste. Er zijn dbms'en die meer opties kennen, een uitgebreidere syntax, of allerlei leuke features, maar daar moet je vaak ook voor betalen in de vorm van performance.
Het is vaak een kwestie van kennis van het betreffende systeem om ook het uiterste eruit te kunnen halen. Voor sommige toepassingen moet je andere tabeltypes toepassen, de configuratie veranderen, je tabelstructuur anders inrichten.
Door het feit dat ook een behoorlijk aantal grote bedrijven MySQL inzet (uit m'n hoofd: Yahoo! Finance, delen van Google, etc.) en uit de resultaten van diverse tests moet ik wel concluderen dat MySQL zeker niet beroerder is dan andere dbms'en.
De juiste toepassing van je gereedschappen is echter altijd essentieel om een goed resultaat te behalen...

[mahi]

Sorry voor de late reply. Ik heb de afgelopen dagen nauwelijks tijd gehad om GoT te bezoeken.

Zoals je kon opmaken uit m'n eerdere berichten weet ik ook wel dat de query die ik opgaf wel degelijk doet wat ie moet doen, maar niet wat ik wil dat ie doet. Ik had me in m'n laatste bericht wat ongelukkig uitgedrukt. En natuurlijk ligt dat aan mij. Ik kom er gewoon niet uit hoe ik de query moet opstellen zodat ik wel het beoogde resultaat krijg.

En inderdaad, ik ken SQL nog steeds niet goed genoeg. Ik heb ondertussen al 3 boeken gekocht over SQL (waaronder 2 specifiek van MySQL) welke aangeraden werden door actieve MySQL gebruikers. Ik heb die boeken natuurlijk grotendeels doorgenomen, maar omdat ik het niet echt dagelijks gebruik maak van SQL slipt heel wat gelezen kennis weer meteen weg. Het probleem dat die boeken ook hebben is dat de theorie er wel allemaal instaat, maar de praktijkvoorbeelden te eenvoudig zijn. Als men nu gewoon eens een paar uitgebreide queries zou opstellen met argumentatie waarom men voor een bepaald iets kiest, dan zou dat voor mij veel duidelijker zijn. Op de MySQL site staan bij de gebruikerscommentaren vaak wel complexe queries, maar die zijn doorgaans gepost door doorwinterde SQL-gebruikers die het niet nodig vinden om uit te leggen wat ze precies doen.

Ik had gewoon gehoopt dat het mogelijk zou zijn om met 1 query tot het beoogde resultaat te komen, maar dat schijnt onmogelijk te zijn.

En met niet bepaald 'je van het' doelde ik meer op de afwezigheid van subqueries in MySQL. Heel wat van m'n voorgaande vragen konden eenvoudig beantwoord worden met een subquery-constructie. Helaas ondersteunt MySQL dit (nog) niet (pas vanaf 4.1) zodat ik steeds dit gebrek moet opvangen in de PHP-code. Pak dan een andere database kun je denken, maar ik ben gebonden aan de database die mijn provider gebruikt...

[ Voor 4% gewijzigd door mahi op 17-01-2004 15:59 ]