Mysql query 4 tabellen voor juiste info

[Verwijderd]

Beste allemaal,

mijn tabelopzet in mysql:

doelpunt(id, speler, wedstrijd)
wedstrijd(id, programma)
programma(id, seizoen)
seizoen(id)

Ik wil nu het aantal doelpunten dat deze speler heeft gemaakt in het huidige seizoen (hier heb ik de id van)...

dan kom ik op iets van:

PHP:

select count(*) as aantal, s.bnaam, s.team
from 006_doelpunt as d, 006_speler as s, 006_wedstrijd, 006_programma
where d.speler = s.id
and 006_wedstrijd.programma = 006_programma.id
and d.wedstrijd = 006_wedstrijd.id 
and 006_programma.seizoen = 2
group by d.speler
order by aantal 
desc limit 150

Nu heb ik al een paar dagen gezocht en alle mogelijke joins geprobeerd. Waar bijvoorbeeld staat "and 006_wedstrijd.programma = 006_programma.id" had ik van gemaakt "LEFT JOIN 006_programma on 006_wedstrijd.programma = 006_programma.id". en dat vooo alle drie de id verwijzingen, maar dan krijg ik over de 2000 resultaten bij de count...

In de mysql manual staat ook dat bij een LEFT JOIN stopt met zoeken als er voor een resultaat NULL uitkomt. Volgens mij heb ik daar niks mee te maken, dit is toch 1 op 1 data controleren. Het tellen van de doelpunten is geen probleem, maar om erbij te checken van welk seizoen ze zijn zorgt voor problemen.

Bij voorbaat dank...

[Verwijderd]

Moet er in de tabel Seizoen niet nog een veld staan om aan te geven welk seizoen het is? Het ID is een uniek sleutelveld neem ik aan, moet je dus niet nog een veld hebben met bijv. seizoensnummer?

Een tabel met 1 veld is een beetje zinloos volgens mij

Goed, toch een opzetje:

[q]
Ik wil nu het aantal doelpunten dat deze speler heeft gemaakt in het huidige seizoen (hier heb ik de id van)...
[/q]
[code=sql]
SELECT COUNT(*) as aantal FROM doelpunt, wedstrijd, programma
WHERE doelpunt.wedstrijd = wedstrijd.id AND wedstrijd.programma = programma.id
AND programma.seizoen = 3 AND doelpunt.speler = 5
[/]
(5 is dus speler-id)
Dit had je al... Ff denken

[ Voor 48% gewijzigd door Verwijderd op 09-03-2006 11:49 ]

[Verwijderd]

Jaja, die zijn er ook. Maar die heb ik niet weergegeven. Seizoen heeft bijvoorbeeld nog een omschrijving en een naam, enz... Maar ik moet alleen gegevens hebben van het huidige seizoen. In dit geval is dit seizoen 2.

Misschien had ik erbij moeten vermelden dat ik niet alle velden weergegeven heb...

[Verwijderd]

Je moet sowieso geen LEFT JOIN gebruiken, want dan haalt hij ook spelers op die niet gescoord hebben. GROUP eens by speler.id?

[Verwijderd]

Dat doe ik toch ook al,

GROUP BY d.speler. In mijn doelpunten tabel heb ik een veld speler staan. Deze staat 1 op 1 met de speler.id tabel. Vandaar ook de vergelijking d.speler = s.id...

Of begrijp ik je comment verkeerd?

[Verwijderd]

Volgens mij benader je e.e.a. verkeerd. Je wil doelpunten per speler weten, maar je begint te lezen bij de tabel 'doelpunten'. Kun je niet beter beginnen met 'spelers', en dan 'doelpunten' daaraan joinen?

Waar je query volgens mij echt de mist in gaat is het feit dat je wedstrijden probeert te koppelen aan een count van meerdere wedstrijden. Dat kan volgens mij sowieso niet.

Nog een tip: gebruik nooit count(*), maar bijv. count(id). Het heeft geen zin om alle velden te tellen, eentje is genoeg.

[martijnvanegdom]

Ik denk dat je aan de gang moet met subquery's (syntax kan afwijken)

selecteer eerst alle programma's van een bepaalt seizoen

SELECT id FROM programma WHERE seizoen = foo

Vervolgens wil je alle wedstrijden van dat programmma
SELECT id FROM wedstrijd WHERE programma = bar

als je deze combineert krijg
SELECT wedstrijd.id FROM wedstrijd WHERE wedstrijd.programma IN
{
SELECT id FROM programma WHERE seizoen = foo
}

Nu heb je alle wedsrijden van een bepaald seizoen in alle programma's van dat seizoen

Vervolgens wil je counten

SELECT COUNT(*) as aantal,
FROM doelpunten
WHERE doelpunten.speler = foo
AND doelpunten.wedsrijd IN
{
SELECT wedstrijd.id
FROM wedstrijd
WHERE wedstrijd.programma IN
{
SELECT id FROM programma WHERE seizoen = foo
}
}

[Verwijderd]

Die subquery's zijn idd handig, maar die kun je pas gebruiken van Mysql 5 geloof ik. Ik heb 4.0.20, jammer dus...

@ wriker,

volgens mij probeer ik geen wedstrijden te kopppelen aan een count van wedstrijden. Een doelpunt is bij mij een tupel in de doelpunten tabel. Met in die tupel: doelpunt(id, spelerid, wedstrijdid, omschrijving, enz..)...

Ik wil gewoon de tupels tellen per speler met als voorwaarde dat een wedstrijdid van een bepaald seizoen is... En of een wedstrijd van een bepaald seizoen is, daar kan ik achter komen door te kijken bij welk programmaid een wedstrijdid hoort...

Dank u

[Verwijderd]

Probeer toch speler eens te joinen, i.p.v. in de WHERE clause op te nemen; dus simpelgezegd:

count(doelpunten) WHERE wedstrijd=foo AND seizoen=bar LEFT JOIN speler

Mijn gevoel zegt dat het zoiets moet zijn (kan er helemaal naast zitten natuurlijk).

[martijnvanegdom]

volgens mij kunnen subquery ook gewoon in 4.0.. ?

[martijnvanegdom]

Een andere oplossing, maar dit is wat minder mooi.. Is je doelpunten database uit te breiden en ook een siezoensID en een programmaID er in op te slaan, dan kun je direct je select doen

[Robbemans]

Nog maar es een keer:

Een GROUP BY werk alleen op alle velden in je select, behalve je geaggregeerde kolom. MySQL gaat hier coulant mee om, maar je krijgt zelden het juiste resultaat.

Je GROUP BY kan dus alleen maar op

code:

s.bnaam, s.team

[ Voor 17% gewijzigd door Robbemans op 09-03-2006 13:51 ]

[Verwijderd]

Alle commentaar in acht geprobeerd te nemen zit ik nu op het volgende:

PHP:

$sql = "select count(d.id) as aantal, s.bnaam, s.team, d.speler
            from 006_doelpunt as d, 006_speler as s, 006_wedstrijd, 006_programma
            where d.speler = s.id
              and 006_wedstrijd.programma = 006_programma.id
              and d.wedstrijd = 006_wedstrijd.id 
              and 006_programma.seizoen = 2
            group by d.speler
            order by aantal 
            desc limit 150";

Ik heb weer geprobeerd te joinen op de aanelkaar gekoppelde id's. Maar dat haalde hele rare dingen uit...

Ik snap eigenlijk niet wat je precies bedoeld Wriker

@ Martijnvanegdom: weet je dat zeker, ik heb net een simpele query geprobeerd, maar die werkte niet...

[ Voor 12% gewijzigd door Verwijderd op 09-03-2006 14:08 ]

[Verwijderd]

Hmm, ik heb even zitten proberen maar ik kom er ook niet uit.
Kun je niet beter ook wedstrijd- en seizoens-id in de doelpuntentabel opslaan? Dus:

doelpunt(id, speler, wedstrijd, programma, seizoen)

Het lost een hoop problemen voor je op en die twee id's die je extra opslaat zijn niet zo'n ramp, lijkt me.

[Verwijderd]

Ik heb het idd zo opgelost. Maar is dit dan geen redundante info?


Bedankt voor alle hulp...

[martijnvanegdom]

Ja het is absoluut redunante info. Maar als het je select probleem oplost dan is niet zo erg.

[jvdmeer]

Volgens mij hoort'ie zo

SQL:

select s.id, d.speler, count(distinct d.id) from doelpunt d
join wedstrijd w on d.wedstrijd=w.id
join programma p on w.programma=p.id
join seizoen s on p.seizoen=s.id
--where s.id=2
group by s.id, d.speler
order by 3

eventueel kan de where nog worden toegevoegd.

[Verwijderd]

Verwijderd schreef op donderdag 09 maart 2006 @ 22:48:
Ik heb het idd zo opgelost. Maar is dit dan geen redundante info?

Nou, ja en nee. Het zijn maar 2 id's, dat is niet echt schokkend veel informatie. Als dat voorkomt dat je, elke keer als je wat meer informatie wil opvragen over een doelpunt, 4 tabellen moet raadplegen, dan zou ik daar gewoon voor gaan.


@ jvdmeer

distinct doelpunt.id is overbodig toch, want ik neem aan dat die id's sowieso uniek zijn?

[jvdmeer]

Verwijderd schreef op vrijdag 10 maart 2006 @ 10:49:
[..]
@ jvdmeer

distinct doelpunt.id is overbodig toch, want ik neem aan dat die id's sowieso uniek zijn?

Waarschijnlijk wel, dus dan is count(id) al voldoende. Uit ervaring blijkt echter dat een count(*) soms verkeerde antwoorden geeft. Vandaar ging ik voor de meest specifieke. Ben alleen benieuwd of dit voor de TS al goed is.