Problemer med flere LEFT JOINS og COUNT

Ligger en del af dit problem i din tabelstruktur?  Du proever, saadan ser det ud for mig, at registrere users med egenskaber koen og aldersgruppe, saa tabel 1 skulle vaere user_id, koen_id, aldersgruppe_id, navn, addresse, og hvad mere du vil vide om users.  For at standardisere koen og aldersgruppe har du saa i tabel 2 koen_id og i tabel 3 aldersgruppe_id.  Saa behoever du ingen join, men kan sige, for eksempel:  SELECT aldersgruppe_id, koen_id, COUNT(koen_id) FROM tabel1 GROUP BY aldersgruppe_id, koen_id.

Man kan kun vælge et køn, men en eller flere aldersgrupper, så jeg har valgt at lægge valget af køn og aldersgrupper i seperate tabeller.
Jeg har også en tabel, user_info, med user_id samt navn, mail osv. Tabellen user_info relaterer direkte til koen_valg og aldersgruppe_valg gennem user_id.
Tabellen aldersgrupper(som skal være udgangspunkt for mine LEFT JOINS) relaterer til aldersgruppe_valg gennem feltet aldersgruppe_id.

Er du stadig interesseret i dette?  Jeg er i gang med at laere mig selv php/mysql (jeg traekker lidt paa et formelt kursus jeg havde i SQL, men det er et par aar tilbage nu).  En strategi er at kikke paa de spoergsmaal der kommer her i Eksperten og proeve at besvare dem og derved faa min viden til at gro.  Jeg var selv interesseret i at laere svaret paa dit problem, derfor har jeg forsket i det, men det har taget sin tid.

Dit direkte spoergsmaal var om der var nogen der havde forslag til at lave en query der vil give det resultat du soegte.  Det kan jeg svare paa:  Nej det er der ikke.  Ingen har reageret undtagen mig, og jeg er nu kommet til den konklusion at det ikke kan goeres i mysql!  Du skal nok have fat i en FULL OUTER JOIN i stedet for en LEFT (eller RIGHT) JOIN, og det understytter mysql ikke.  Jeg har paa google fundet nogle forslag til at omgaas denne begraensning, blandt andet ved at lave to queries og bruge UNION, men det har ikke haft de soegte resultat.

En ting synes sikker:  Hvis du vil have alle users representeret i svaret paa din query saa er du noed til ogsaa at joine med din user-info tabel.

Jeg har lavet en lille database med de foelgende tabeller og indhold:

koen_valg
id    user_id        koen
1    1        m
2    2        k
3    4        m
4    5        m
5    6        k

aldersgruppe_valg
id    user_id        aldersgruppe_id
1    1        2
2    3        2
3    4        3*
4    5        3*
5    6        3*

aldersgrupper
id        aldersgruppe
1        gruppe1
2        gruppe2
3        gruppe3

user_info
id    navn
1    aaa
2    bbb
3    ccc
4    ddd
5    eee
6    fff

Som du ser har jeg aldersgrupper hvor der ingen brugere er i og brugere hvor enten koen eller aldersgruppe ikke er opgivet.

Saa har jeg eksperimenteret mig frem til denne query: 

SELECT  aldersgruppe, koen, COUNT(koen)
FROM user_info u
LEFT JOIN aldersgruppe_valg av ON u.id = av.user_id
LEFT JOIN aldersgrupper a ON av.aldersgruppe_id = a.id
LEFT JOIN koen_valg k ON u.id = k.user_id
GROUP BY aldersgruppe, koen

Den giver det foelgende resultat:

aldersgruppe    koen    COUNT( koen ) 
NULL         kvinde     1
gruppe2     NULL     0
gruppe2     mand     1
gruppe3     kvinde     1
gruppe3     mand     2

Den viser korrekt at der er 1 kvinde der ikke tilhoerer nogen aldersgruppe.  Saa viser den ogsaa korrekt at der er 0 "ikke-mand" i gruppe 2, men den skriver ikke kvinde.

Saa ville jeg vaere smart og normalisere din database (jeg gaar ud fra at en user kun kan tilhoere en aldersgruppe) ved at udvide user_info med oplysning om koen og aldersgruppe og saa, for at bevare referentiel integritet, lave look-up tabeller for aldersgruppe og koen.  De foelgende tabeller:

user_info1
id    navn    mail    koen    aldersgruppe
1    aaa    a@a    m    gruppe2
2    bbb    b@b    k
3    ccc    c@c        gruppe2
4    ddd        m    gruppe3
5    eee    e@e    m    gruppe3
6    fff    f@f    k    gruppe3   

aldersgruppe1
id    aldersgruppe
1    gruppe1
2    gruppe2
3    gruppe3

koen1
id    koen
1    m
2    k

I saa fald er queriet meget simpler uden joins:

SELECT aldersgruppe, koen, COUNT(koen) from user_info1 GROUP BY aldersgruppe, koen

Men -- resultatet er noejagtigt det samme som ovenfor.

Saa direkte i mysql gaar den ikke, tror jeg.

Jeg tror det kan lade sig goere i php.  Af nysgerrighed vil jeg proeve det ud, i morgen hvis jeg faar tid.

Ja, jeg er meget interesseret og tak fordi du bruger tid på det.

Jeg har stadig ikke en løsning, men jeg har også overvejet det med at skulle joine også med min user-info tabel.

Og man kan tilvælge op til 5-7 aldersgruppe - de er meget overlappende, så man kan tilhøre flere.

Tidligere havde jeg køn samt aldersgruppevalg i user_info tabellen, men det gav andre problemer. Faktisk var det arne_v som rådede mig til at lave det på denne måde - måske har jeg misforstået det :-)

Jeg har ladet det ligge i et stykke tid og arbejdet på noget andet for at komme tilbage til det, men det kan være du kan løse det :-)

Venlig hilsen
Kim

For at du ved det saa gik jeg i gang med at kikke igen paa mit gamle SQL kursus.  Maaske skal det loeses ved hjaelp af sub-selects.  Det goer jeg naturligvis for mit eget bedste fordi jeg uden tvivl vil faa brug for mere solid SQL viden.  Men hvis det jeg finder frem til kan loese dig problem, saa meget des bedre.

At aldersgrupperne er overlappende og en user saaledes kan tilhoere flere aldersgrupper var det jeg ikke vidste.  Ok, det hoerer saa med.

Jeg har lavet det - paa en maade.

(1)  Jeg har ikke fundet ud af at faa det oenskede resultat direkte i en mysql query.  Du vil have et resultat der indeholder information der IKKE er i databasen, saasom en rapport over hvor mange kvinder der ikke er i aldersgruppe 1.  Det er muligt at det i et fuld SQL program kan laves med funktioner saasom FULL OUTER JOIN, nested subqueries, o.s.v.  Jeg gennemsaa mit gamle SQL kursus for at blive smartere paa disse felter.  Men det ser ud til at mysql er et begraenset "Relational Database Management System" og mysql understoetter (tilsyneladende) ikke FULL OUTER JOIN og nested subqueries (og med begraenset stoette for VIEW.)

(2)  Men jeg tror slet ikke at det er meningen at brugere af en software application skal have oplysninger direkte fra SQL queries men fra rapporter som programmoeren bygger og fylder med data fra SQL query og der fylder ind med, for eksempel, 0 hvis der ingen oplysninger er.  Jeg har lavet saadan en rapport paa http://christianjorgensen.be/luckymik.php.
Den spoerger paa den foelgende database og fylder ud med 0 paa de rette steder - her er databasen:

user_info
id  navn  koen 
      1 aaa mand
      2 bbb kvinde
      3 ccc kvinde
      4 ddd mand
      5 eee mand
      6 fff kvinde

aldersgruppe_valg

id  user_id  aldersgruppe_id 
      1 1 2
      2 3 2
      3 4 3
      4 5 3
      5 6 3
      6 1 4
      7 1 5
      8 6 6

aldersgrupper

id  aldersgruppe 
      1 gruppe1
      2 gruppe2
      3 gruppe3
      4 gruppe4
      5 gruppe5
      6 gruppe6

Hvis du kikker paa koden saa maa du ikke grine.  Jeg har lavet 12 sql queries hvor jeg helt klart skulle have lavet en funktion, men det bliver det naeste jeg skal laere.

(3)  En begraensning er at aldersgrupperne er "hard-coded."  Hvis du putter en ny aldersgruppe i databasen saa skal php koden aendres.  Jeg proevede at lave det dynamisk saa tabellen opbygges over de til enhver tid eksisterende aldersgrupper, men det bliver ogsaa for engang i fremtiden.

(4)  Jeg tillod mig at putte koen i user_info.  Det er korrekt at holde aldersgruppe udenfor naar aldersgrupper overlapper og en user kan hoere til flere, men eftersom en user kun kan have et koen maa det vaere en misforstaaelse at lave en saerskilt koen_valg tabel.  Til gengaeld boer der vaere en koen tabel med de eksisterende koen (for eksempel "mand" og "kvinde" og saa en fremmednoegle i user_id saa kun disse koen kan bruges.  Ellers risikerer du ogsaa at faa "m", "k" "kv" o.s.v.  Men hvis du insisterer paa en koen_valg tabel saa kan jeg aendre koden.

Jeg haaber du kan bruget noget af dette.  Jeg selv har, som sagt, brugt dit spoergsmaal til at laere om mysql og php.

Jeg forstaar ikke, hvis jeg klikker paa linket http://christianjorgensen.be/luckymik.php saa faar jeg en fejlmelding, men hvis jeg fylder det ud direkte i browseren saa faar jeg siden med rapporten.

Der er et punktum sidst i linket :-)

Du har ikke fået lagt koden med ind

Ok, nu har jeg saniteret koden saa mysql searchen ligger i en funktion.  Samme database som ovenfor, og samme url for resultatet.  Som du kan se saa har jeg i html lavet en tabel for antal af maend og kvinder i hver aldersgruppe.  For hver celle i tabellen laver jeg en mysql query ved at kalde funktionen med aldersgruppenavn og kvinde/mand.  Hvis der er et resultat saa indsaetter jeg resultatet i cellen, ellers indsaetter jeg nul.  Den fulde html og php kode er nedenfor.

Jeg antager at det var den INFORMATION du var ude efter selv om vi ikke fandt en METODEN med direkte mysql query.  (Hvis du vil have koen_valg i en saerskilt tabel saa skal du bare laegge en ekstra join in i funktionen: saaledes: ....LEFT JOIN aldersgruppe_valg av ON a.id = av.aldersgruppe_id  LEFT JOIN user_info u ON av.user_id = u.id LEFT JOIN koen_valg k ON u.id = k.user_id....).

Jeg er glad for at jeg ikke skal tjene mit broed ved det, for det har taget mig lang tid.  Jeg haaber at resultatet er nyttigt.  Jeg synes tiden er inde til at lukke spoergsmaalet og uddele punkter.

Her er koden:

<html>
<head><title></title></head>
<body>
<?
  $link = mysql_connect ('christianjorgensen.be.mysql', 'christianjoygen', '*******') or die(mysql_erorr());
  mysql_select_db('christianjoygen') or die('Could not select database');
  function findrow($gruppeid, $koenid)
{
    $query = "SELECT aldersgruppe, koen, COUNT( koen )
    FROM aldersgrupper a
    LEFT JOIN aldersgruppe_valg av ON a.id = av.aldersgruppe_id
    LEFT JOIN user_info u ON av.user_id = u.id
    WHERE aldersgruppe = " . $gruppeid . " AND koen = " . $koenid . "
    GROUP BY aldersgruppe, koen";   
    $result = mysql_query($query);
    if($row = mysql_fetch_array($result)) return $row[2];
    else return "0";
}?>
<table width="500px">
<tr><td width="33%">Aldersgruppe</td><td width="33%">antal kvinder</td><td>antal maend</td></tr>
<tr><td>Gruppe1</td>
<td><? echo findrow("'gruppe1'", "'kvinde'");?></td>
<td><? echo findrow("'gruppe1'", "'mand'");?></td></tr>
<tr><td>Gruppe2</td><td>
<? echo findrow("'gruppe2'", "'kvinde'"); ?></td><td>
<? echo findrow("'gruppe2'", "'mand'"); ?></td></tr>
<tr><td>Gruppe3</td><td>
<? echo findrow("'gruppe3'", "'kvinde'"); ?></td>
<td><? echo findrow("'gruppe3'", "'mand'"); ?></td></tr>
<tr><td>Gruppe4</td>
<td><? echo findrow("'gruppe4'", "'kvinde'"); ?></td>
<td><? echo findrow("'gruppe4'", "'mand'"); ?></td></tr>
<tr><td>Gruppe5</td>
<td><? echo findrow("'gruppe5'", "'kvinde'"); ?></td>
<td><? echo findrow("'gruppe5'", "'mand'"); ?></td></tr>
<td>Gruppe6</td>
<td><? echo findrow("'gruppe6'", "'kvinde'"); ?></td>
<td><? echo findrow("'gruppe6'", "'mand'"); ?></td></tr>
</table></body></html>

Hej Christian

Det ser godt ud, jeg når først at kikke mere på det i løbet af i morgen, vil jeg tro. Men jeg kan godt se du har brugt meget tid på det (og forhåbentlig lært en masse).

Venlig hilsen Kim

Hej Christian

Ud fra dit store arbejde har jeg fundet frem til følgende sql, som fungerer. Udover gruppering på køn og aldersgrupper, har jeg også gruppering på byer/landsdele. Det fungerer sådan at man kan vælge en by/landsdel på en dropdownliste og så får man resultatet for området. Er der 0 personer vises rækken ikke, men det må jeg så leve med.
Tak for hjælpen :-)

SELECT startalder, slutalder, aldersgruppe, koen, aldersgrupper.aldersgruppe_id AS aid, COUNT(koen_valg.user_id) AS antal
FROM aldersgrupper
LEFT JOIN aldersgruppe_valg
ON aldersgrupper.aldersgruppe_id=aldersgruppe_valg.aldersgruppe_id
INNER JOIN koen_valg
ON koen_valg.user_id=aldersgruppe_valg.user_id
INNER JOIN by_valg
ON by_valg.user_id=koen_valg.user_id
WHERE by_valg.bynavn_id= '$omraade_id'
GROUP BY aldersgruppe, koen)
UNION
(SELECT startalder, slutalder, aldersgruppe, koen, aldersgrupper.aldersgruppe_id AS aid, COUNT(koen_valg.user_id) AS antal
FROM aldersgrupper
LEFT JOIN aldersgruppe_valg
ON aldersgrupper.aldersgruppe_id=aldersgruppe_valg.aldersgruppe_id
INNER JOIN koen_valg
ON koen_valg.user_id=aldersgruppe_valg.user_id
INNER JOIN landsdele_valg
ON landsdele_valg.user_id=koen_valg.user_id
WHERE landsdele_valg.landsdel_id= '$omraade_id'
GROUP BY aldersgruppe, koen)
ORDER BY aid, koen