[SQL] Aantal evenemten per lid tellen

[KennieNL]

Mensen ik zit met een probleem waar ik nu al even tegen zit te hikken, ik heb een 3 tabels:

tblEvenementen,tblEvenementenDeelnemers,tblLeden

Elk lid kan aan meerdere evenementen meedoen, deze wordt dmv evenementid en lidid in de tblEvenementDeelnemers opgeslagen

nu wil ik een COUNT() uitvoeren op het aantal evenementen wat een lid heeft begewoont in een bepaald jaar.


Ik heb nu de query:

SQL:

SELECT tblLeden.ID, tblLeden.Voornaam, tblLeden.Achternaam, COUNT( tblEvenementenDeelnemers.LidID )
FROM tblLeden
LEFT JOIN tblEvenementenDeelnemers ON ( tblLeden.id = tblEvenementenDeelnemers.LidID) LEFT JOIN tblEvenementen ON (tblEvenementen.id = tblEvenementenDeelnemers.EvenementID AND YEAR(tblEvenementen.Aanvang) = 2008) GROUP BY tblLeden.ID

de query geeft nu ook evenementen aan die NIET in 2008 gebeurd zijn.

ik heb even geen idee waar ik het moet zoeken, waarschijnlijk de 2de LEFT JOIN, maar ik kan me niet echt bedenken wat het wel kan zijn.

Kan iemand me een push in de goede richting geven (of een werkende query, maar een push in de goede richting zou al fijn zijn )?

[UltimateB]

Volgends mij "AND YEAR" veranderen in "WHERE YEAR".

[pedorus]

Waar doe je een count op? Waar had dat op gemoeten?

En gebruik voortaan [code=sql]...[/code]

[KennieNL]

pedorus schreef op zondag 07 december 2008 @ 23:17:
Waar doe je een count op? Waar had dat op gemoeten?

En gebruik voortaan [code=sql]...[/code]

arghhh!, tblEvenementen.id natuurlijk
dom dom dom dom

anyway thanks!, over [code] tags, ja die had ik toegevoegd maar wouden niet helemaal, had ik ze maar weggehaald.

iig bedankt!

[RobIII]

KennieNL schreef op zondag 07 december 2008 @ 23:25:
anyway thanks!, over [code] tags, ja die had ik toegevoegd maar wouden niet helemaal, had ik ze maar weggehaald.

Ik heb even in je diff gekeken:

KennieNL schreef op zondag 07 december 2008 @ 23:25:
[code=sql]SELECT ...GROUP BY tblLeden.ID[/sql]

Dat gaat niet werken nee Je opent een [code] tag en sluit met een [/sql] tag Zie code tags

[ Voor 19% gewijzigd door RobIII op 08-12-2008 00:35 ]

[KennieNL]

RobIII schreef op maandag 08 december 2008 @ 00:04:
[...]

Ik heb even in je diff gekeken:


[...]

Dat gaat niet werken nee Je opent een [code] tag en sluit met een [/sql] tag Zie code tags

kun je nagaan dat ik al aan het slapen was

thanks in ieder geval

[KennieNL]

Om hier op terug te komen:

SQL:

SELECT tblLeden.ID, tblLeden.Voornaam, tblLeden.Achternaam, COUNT( tblEvenementen.id )
FROM tblLeden
LEFT JOIN tblEvenementenDeelnemers ON ( tblLeden.id = tblEvenementenDeelnemers.LidID)
LEFT JOIN tblEvenementen ON (tblEvenementen.id = tblEvenementenDeelnemers.EvenementID AND YEAR(tblEvenementen.Aanvang) = 2008)
GROUP BY tblLeden.ID
ORDER BY Achternaam,Voornaam ASC

wat zou een goede manier zijn om te checken if count(tblEvenementen.id = 0) ?

Ik kan natuurlijk door mn tblLeden loopen en elke meer een multiple SELECT doen, maar dit vindt ik niet erg netjes en is zeker niet resource zuinig.

Je kunt namelijk geen WHERE meer toepassen na een GROUP ?

[pedorus]

KennieNL schreef op maandag 08 december 2008 @ 19:29:
Je kunt namelijk geen WHERE meer toepassen na een GROUP ?

Maar je kan wel HAVING gebruiken. Maar dan nog is 0 een bijzonder geval. Als je geen 0 wil hebben, dan kun je natuurlijk een inner join gebruiken. Als je juist 0 wil hebben, dan kun je bijvoorbeeld NOT EXISTS gebruiken. Zie een willekeurige site over sql...

[KennieNL]

pedorus schreef op maandag 08 december 2008 @ 19:58:
[...]

Maar je kan wel HAVING gebruiken. Maar dan nog is 0 een bijzonder geval. Als je geen 0 wil hebben, dan kun je natuurlijk een inner join gebruiken. Als je juist 0 wil hebben, dan kun je bijvoorbeeld NOT EXISTS gebruiken. Zie een willekeurige site over sql...

als ik volgende toevoeg na de GROUP BY werkt het:
HAVING COUNT( tblEvenementen.id ) = 0


INNER JOIN doet niet precies wat ik wil, omdat tblEvenementenDeelnemers wel results kan hebben, maar dan tblEvenementen met de jaar filter weer niet.

tnx voor de link trouwens, bookmarked

[pedorus]

KennieNL schreef op maandag 08 december 2008 @ 20:17:
als ik volgende toevoeg na de GROUP BY werkt het:
HAVING COUNT( tblEvenementen.id ) = 0

INNER JOIN doet niet precies wat ik wil, omdat tblEvenementenDeelnemers wel results kan hebben, maar dan tblEvenementen met de jaar filter weer niet.

Bij = 0 zou ik dan ook NOT EXISTS gebruiken ipv COUNT als performance uitmaakt, die inner join was bij >0.

[Alain]

Waarom zou een where niet werken?

Testcase:

SQL:

CREATE TABLE `Person` (
  `personId`        INT UNSIGNED NOT NULL,
  `personName`      VARCHAR(255) NOT NULL
);

CREATE TABLE `Event` (
  `eventId`     INT UNSIGNED NOT NULL,
  `eventDate`       DATE NOT NULL
);

CREATE TABLE `Participant` (
  `personId`        INT UNSIGNED NOT NULL,
  `eventId`     INT UNSIGNED NOT NULL
);

INSERT INTO `Person`
VALUES          (1, 'Persoon 1'),
            (2, 'Persoon 2');
INSERT INTO `Event`
VALUES          (1, '2007-12-30'),
            (2, '2007-12-31'),
            (3, '2008-01-01'),
            (4, '2008-01-02');
INSERT INTO `Participant`
VALUES          (1, 1),
            (1, 2),
            (1, 3),
            (1, 4),
            (2, 1),
            (2, 2),
            (2, 3);

SELECT          `Pa`.`personId`, `personName`, COUNT(`Pa`.`personId`) `Count events`
FROM            `Participant` `Pa`
LEFT JOIN       `Person` `P`
ON          `P`.`personId` = `Pa`.`personId`
LEFT JOIN       `Event` `E`
ON          `E`.`eventId` = `Pa`.`eventId`
WHERE            YEAR(`E`.`eventDate`) = 2008
GROUP BY        `Pa`.`personId`;

Levert op:

code:

+----------+------------+--------------+
| personId | personName | Count events |
+----------+------------+--------------+
|        1 | Persoon 1  |            2 |
|        2 | Persoon 2  |            1 |
+----------+------------+--------------+
2 rows in set (0.00 sec)

[KennieNL]

AlainS schreef op maandag 08 december 2008 @ 21:47:
Waarom zou een where niet werken?

Testcase:

SQL:

CREATE TABLE `Person` (
  `personId`        INT UNSIGNED NOT NULL,
  `personName`      VARCHAR(255) NOT NULL
);

CREATE TABLE `Event` (
  `eventId`     INT UNSIGNED NOT NULL,
  `eventDate`       DATE NOT NULL
);

CREATE TABLE `Participant` (
  `personId`        INT UNSIGNED NOT NULL,
  `eventId`     INT UNSIGNED NOT NULL
);

INSERT INTO `Person`
VALUES          (1, 'Persoon 1'),
            (2, 'Persoon 2');
INSERT INTO `Event`
VALUES          (1, '2007-12-30'),
            (2, '2007-12-31'),
            (3, '2008-01-01'),
            (4, '2008-01-02');
INSERT INTO `Participant`
VALUES          (1, 1),
            (1, 2),
            (1, 3),
            (1, 4),
            (2, 1),
            (2, 2),
            (2, 3);

SELECT          `Pa`.`personId`, `personName`, COUNT(`Pa`.`personId`) `Count events`
FROM            `Participant` `Pa`
LEFT JOIN       `Person` `P`
ON          `P`.`personId` = `Pa`.`personId`
LEFT JOIN       `Event` `E`
ON          `E`.`eventId` = `Pa`.`eventId`
WHERE            YEAR(`E`.`eventDate`) = 2008
GROUP BY        `Pa`.`personId`;

Levert op:

code:

+----------+------------+--------------+
| personId | personName | Count events |
+----------+------------+--------------+
|        1 | Persoon 1  |            2 |
|        2 | Persoon 2  |            1 |
+----------+------------+--------------+
2 rows in set (0.00 sec)

de where in mijn geval gaat over de count.. en de count is pas goed na de group by.

@Pedorus: preformance maakt niet heel veel uit, maar toch altijd handig om te weten. Waar ik wel nog niet helemaal aan uit kom is hoe ik de NOT EXISTS in dit voorbeeld kan toepassen.