[MySQL] COUNT op rij uit JOIN geeft geen resultaat

[Anoniem: 96523]

Ik heb een relatief simpele MySQL query, maar krijg niet de verwachte resultaten terug.

De query moet berichten (DM's) ophalen uit de database inclusief het aantal bijlagen dat erbij hoort.

Hier een (gestript) voorbeeld van de query:

SQL:

    SELECT
        `b`.`id`,
        `b`.`titel`,
        `b`.`onderwerp`,
        COUNT(`bb`.`bijlage_id`) as `bijlagen`
    FROM
        `berichten` as `b`
    LEFT JOIN
        `bericht_bijlagen` as `bb`
    ON
        `bb`.`bericht_id` = `b`.`id`

Dit werkt perfect, maar alleen als er ook daadwerkelijk bijlagen zijn. Wanneer er geen bijlagen zijn voor het bericht, dan wordt deze niet getoond in de resultaten. Om dezelfde reden wordt het met een JOIN gedaan, zodat bijlagen niet verplicht zijn.

Het blijkt dat de JOIN de waarde NULL teruggeeft, waardoor de COUNT niets doet en dus geen resultaat kan vinden. Dit heb ik proberen op te lossen met NULLIF():

SQL:

COUNT(NULLIF(`bb`.`bijlage_id`,0)) as `bijlagen`

maar helaas werkt dit ook niet, omdat MySQL waarschijnlijk vindt dat de hele tabel niet bestaat.

Nu kan ik natuurlijk met meerdere queries werken, of met interne SELECT's, maar volgens mij moet mijn idee wel mogelijk zijn.
Is er iemand die hier een oplossing voor heeft, of moet ik gewoon "smerig" meerdere queries draaien om het verwachte resultaat te krijgen?

[RobIII]

SQL:

Coalesce(COUNT(`bb`.`bijlage_id`),0)

[Anoniem: 82076]

Mis je niet ergens een GROUP BY?

[Anoniem: 96523]

RobIII schreef op woensdag 29 juni 2011 @ 14:07:

SQL:

Coalesce(COUNT(`bb`.`bijlage_id`),0)

Ik krijg hiermee hetzelfde resultaat als mijn voorbeeld query (of met NULLIF, welke in dit geval exact hetzelfde doet).

Anoniem: 82076 schreef op woensdag 29 juni 2011 @ 14:40:
Mis je niet ergens een GROUP BY?

Had ik dan niet meerdere keren dezelfde row moeten krijgen wanneer ik een GROUP BY mis? Ik krijg nu helemaal geen rijen terug wanneer er geen bijlagen zijn. Een GROUP BY is juist nodig wanneer je teveel rijen terug krijgt


Het lijkt erop dat de COUNT functie niet om kan gaan met de waarde NULL, want een JOIN geeft NULL terug wanneer er geen resultaat is. Er wordt nu dus COUNT( NULL ) gedaan, wat technisch niet kan.
De reden waarom NULLIF en COALESCE in dit geval geen uitkomst bieden is mij overigens een raadsel.

Het feit dat er geen foutmelding wordt gegeven kan ik me voorstellen, maar dat er dan maar helemaal niets wordt gedaan vind ik wel vreemd.


P.S. dit probleem is niet van levensbelang en meer een observatie die vraagtekens bij mij oproept. Ik heb dus al een query die werkt (ipv count een subquery), maar gezien de technische "fout" (IMHO) wel een interessant punt om over door te denken.

[RobIII]

Anoniem: 82076 schreef op woensdag 29 juni 2011 @ 14:40:
Mis je niet ergens een GROUP BY?

Dat sowieso inderdaad; helemaal gemist
Hoe werkt dat GROUP BY nu eigenlijk?

[Anoniem: 96523]

Ok, ik heb nu de volgende query en het lijkt te werken

SQL:

    SELECT 
        `b`.`id`, 
        `b`.`titel`, 
        `b`.`onderwerp`, 
        COUNT(`bb`.`bijlage_id`) as `bijlagen` 
    FROM 
        `berichten` as `b` 
    LEFT JOIN 
        `bericht_bijlagen` as `bb` 
    ON 
        `bb`.`bericht_id` = `b`.`id`
    GROUP BY
        `bb`.`bericht_id`

(inderdaad, een GROUP BY)

Hoewel MySQL een GROUP BY niet verplicht (is een brakke database ) vind ik het vreemd dat het resultaat zo vreemd is. Ik verwacht nu dat alle bijlagen behorend tot een bericht gegroepeerd worden en een COUNT dus altijd op 1 komt. zelf verkeerd gelezen...

welkom in de wondere wereld van SQL

[LazySod]

Anoniem: 96523 schreef op woensdag 29 juni 2011 @ 15:18:
Ok, ik heb nu de volgende query en het lijkt te werken

SQL:

    SELECT 
        `b`.`id`, 
        `b`.`titel`, 
        `b`.`onderwerp`, 
        COUNT(`bb`.`bijlage_id`) as `bijlagen` 
    FROM 
        `berichten` as `b` 
    LEFT JOIN 
        `bericht_bijlagen` as `bb` 
    ON 
        `bb`.`bericht_id` = `b`.`id`
    GROUP BY
        `bb`.`bericht_id`

(inderdaad, een GROUP BY)

Hoewel MySQL een GROUP BY niet verplicht (is een brakke database ) vind ik het vreemd dat het resultaat zo vreemd is. Ik verwacht nu dat alle bijlagen behorend tot een bericht gegroepeerd worden en een COUNT dus altijd op 1 komt. zelf verkeerd gelezen...


[...]

Die GROUP BY is inderdaad erg brak dan. Vervang hem door

SQL:

GROUP BY b.id, b.titel, b.onderwerp

en dan werkt het op andere databases ook

of alternatief, aangezien b.id waarschijnlijk uniek is:

SQL:

select b.id,
         max(b.titel),
         max(b.onderwerp),
         count(bb.bijlage_id) 

... 

group by b.id

[Gomez12]

LazySod schreef op woensdag 29 juni 2011 @ 15:25:
[...]
of alternatief, aangezien b.id waarschijnlijk uniek is:

SQL:

select b.id,
         max(b.titel),
         max(b.onderwerp),
         count(bb.bijlage_id) 
... 
group by b.id

Hmm, wat betekent een max op een textveld dan? Persoonlijk zou ik liever voor de group by op id,titel,onderwerp gaan. Als dan later ooit (vanwege reden x/y/z) titel/onderwerp/id niet meer uniek is dan krijg je voorspelbaar gedrag (meerdere regels) ipv dat je bij toeval gewoon 1 regel terugkrijgt die voldoet aan max voor textvelden maar die niet is wat je zocht ( veel plezier met debuggen )

[LazySod]

Die max betekent in dit geval niets - omdat er maar 1 record is (aangezien het ID veld al uniek is). Als ID niet meer uniek is dan is de max de hoogste string value (en die is, in Oracle in ieder geval, afhankelijk van NLS settings).

De group by over meerdere kolommen geniet daarom ook mijn voorkeur, maar je moet een manier kiezen om van de "not a group by expression" error af te komen en het gebruik van max (en min) kunnen daar helpen.

[Voor 16% gewijzigd door LazySod op 29-06-2011 21:15]

[ZaZ]

Start je sessie met ONLY_FULL_GROUP_BY, of nog beter, stel het in op je server.
Dat voorkomt dit soort fouten