[SQL] Aantal familieleden join

[Verwijderd]

Ik heb nu de query

code:

 SELECT families.famnaam, families.don, members.name, COUNT( members.id ) AS famleden
FROM families
INNER JOIN members ON ( families.don = members.id )
LIMIT 0 , 30

(SQL fout is dat ik moet Group By'en, als ik dit doe (op famleden, want ik wil de meeste leden bovenaan) krijg ik dat ik daaar niet op kan groupen)

Doel query:
families.famnaam,families.don (maar dan niet de INT versie (id van members) maar de members.name),aantal members dat members.family families.id heeft

query moet een familielijst opleveren, ik zie mezelf als goed in PHP en MySQL, maar als de query lukt zou ik eerlijk gezegd ook niet meer weten hoe ik hem mysql_fetch_assoc() gefetch kan opvragen, dus als je dat erbij zou kunnen zetten;-)

Bvd, Timo

[whoami]

Sorry hoor, maar ik snap niet wat je bedoeld. Probeer eens met duidelijke zinnen (en minder spelfouten) uit te leggen wat je nu eigenlijk wilt bereiken ?

Wat heeft group by te maken met het feit dat je de meeste leden bovenaan wilt ? Dat doe je door te sorteren.
Verder moet, als je een aggregate functie gebruikt, je ieder veld dat je in je select lijst staan hebt, en geen aggregate is, in je group by opnemen. Zie ook: klik

Verder wil ik je er ook even op wijzen dat dit hier geen 'code-voorkauw' forum is.

[_Quinten_]

MySQL geeft bij zulke queries een fout. COUNT en gewoon dingen ophalen kan niet zonder GROUP BY te gebruiken in je query.

[ Voor 74% gewijzigd door _Quinten_ op 28-11-2007 19:01 ]

[whoami]

^^ en maar goed ook ...

[_Thanatos_]

Herschrijven naar een subquery?

SQL:

SELECT f.famnaam, f.don, (
   SELECT COUNT(*) FROM members
   WHERE f.don = id
) AS famleden
FROM families AS f

Ofzo? Niet getest enzo, het is maar een schop is de richting.

[Cascade]

Dus als ik het goed begrijp wil je de members tabel gebruiken om de naam van de 'don' erbij te zoeken maar ook om het aantal familieleden te tellen? Of zit ik nou totaal mis?

Zoiets?

SQL:

SELECT families.famnaam, families.don, don.naam, COUNT(members.id) AS famleden
FROM families
INNER JOIN members AS don ON don.id=families.don
LEFT JOIN members ON members.familyid=families.id
GROUP BY families.famnaam
ORDER BY famleden DESC
LIMIT 0,30;

Wat snap je niet van de fetch_assoc? Je krijgt een array terug met associatie veldnaam => veldwaarde, doe anders gewoon een var_dump of print_r op het resultaat, dan zie je meteen wat het is. Het staat ook prachtig beschreven bij de PHP functions, heb je daar wel gekeken? http://nl2.php.net/mysql_fetch_assoc

[Verwijderd]

Verwijderd schreef op woensdag 28 november 2007 @ 18:31:
(SQL fout is dat ik moet Group By'en, als ik dit doe (op famleden, want ik wil de meeste leden bovenaan) krijg ik dat ik daaar niet op kan groupen)

dus dat je GROUP BY moet gebruiken had ik al... maar het staat niet in mijn code omdat het niets toevoegd..

DOEL:
De query moet het aantal familieleden, de familienaam en de naam van de don ophalen
aantal familieleden = count(members.id) waar members.family = families.id
familienaam = families.famnaam
naam don = members.name waar members.id = families.don

hoop dat dat wat ophelderd.
@Voldemort2: als jij dit op een andere manier weet op te lossen (dus zonder code) dan mag je dat proberen, maar lijkt me heel sterk

EDIT: Zie net je bericht Thanatos, dat werkt in principe, alleen nog even zorgen dat hij niet de id van de don (wat nu het geval is) maar de naam selecteerd, maar dat zal wel lukken, is een snellere oplossing denk ik dan 1 met 2 joins

nog even voor de compleetheid:

code:

SELECT f.famnaam, f.don, (
        SELECT COUNT(*) FROM members
            WHERE f.id = family
        ) AS famleden,( SELECT name FROM members WHERE f.don = id) AS don
FROM families AS f

werkt perfect!

[ Voor 23% gewijzigd door Verwijderd op 28-11-2007 19:19 ]

[Voutloos]

Verwijderd schreef op woensdag 28 november 2007 @ 19:14:
nog even voor de compleetheid:

code:

SELECT f.famnaam, f.don, (
        SELECT COUNT(*) FROM members
            WHERE f.id = family
        ) AS famleden,( SELECT name FROM members WHERE f.don = id) AS don
FROM families AS f

werkt perfect!

Een subquery voor die count is grote onzin, kan gewoon in hoofdquery (en hoofdquery dus met join en group by). Joinen kan gewoon eenvoudig en zal bovendien hier betere performance geven.
De subquery voor de name van de don is overigens wel nodig.

[bigbeng]

Verwijderd schreef op woensdag 28 november 2007 @ 19:14:
[...]
EDIT: Zie net je bericht Thanatos, dat werkt in principe, alleen nog even zorgen dat hij niet de id van de don (wat nu het geval is) maar de naam selecteerd, maar dat zal wel lukken, is een snellere oplossing denk ik dan 1 met 2 joins

Een subquery is niet inherent sneller dan een join, maar leest vaak wel iets eenvoudiger. Vaak is het zelfs beter om iets met een join te doen ipv een subquery, indien mogelijk.

[Verwijderd]

zal het dan met join voor aantal leden en subquery voor naam don doen;-)

bedankt allemaal

[Cascade]

Ik moest lachen

Maar dit is wel een interessante discussie eigenlijk, dat hele joins vs. subqueries verhaal. Wanneer gebruik je nou wat? Zijn er vuistregels voor?

Net dit gelezen: http://p2p.wrox.com/topic.asp?TOPIC_ID=58142.

[P_de_B]

In principe zou ik zeggen gebruik een JOIN. Databases zijn bij uitstek geschikt om met JOINs te werken, en in veel gevallen zal een JOIN het snelst zijn. Een beetje database zal trouwens veelal een subquery hetzelfde uitvoeren als een JOIN, dus dan maakt het performancetechnisch niet uit.

Mijn vuistregel: gebruik een JOIN, en als het echt niet anders kan dan een subquery. Je kun took uiteraard per geval gaan testen wat het meest snelle is.

[Verwijderd]

Cascade schreef op woensdag 28 november 2007 @ 19:14:
Zoiets?

SQL:

SELECT families.famnaam, families.don, don.naam, COUNT(members.id) AS famleden
FROM families
INNER JOIN members AS don ON don.id=families.don
LEFT JOIN members ON members.familyid=families.id
GROUP BY families.famnaam
ORDER BY famleden DESC
LIMIT 0,30;

Zal waarschijnlijk prima werken bij MySQL, maar zo ongeveer iedere andere database zal 'm niet accepteren: wanneer je GROUP BY gebruikt moeten volgens de SQL regels alle velden die niet in de GROUP BY zitten geaggregeerd zijn, en families.don en don.naam zijn dat hier niet.

Jij en ik weten (of hopen) dat die 2 velden binnen de GROUP BY steeds dezelfde waarden zullen opleveren, maar je database weet dat op dat moment nog niet. MySQL geeft gewoon de eerste (of de laatste?) die 'ie tegenkomt terug, maar een SQL92 compliant database systeem zal gewoon een error teruggeven.

[EfBe]

Voutloos schreef op woensdag 28 november 2007 @ 19:25:
[...]
Een subquery voor die count is grote onzin, kan gewoon in hoofdquery (en hoofdquery dus met join en group by). Joinen kan gewoon eenvoudig en zal bovendien hier betere performance geven.
De subquery voor de name van de don is overigens wel nodig.

Waarom is die scalar subquery in de projection grote onzin? Net of jouw benadering met groupby altijd kan. een scalar subquery kan altijd, een groupby alleen in sommige gevallen, of je moet mysql gebruiken en dan kan groupby bizarre vormen aannemen.

bigbeng schreef op woensdag 28 november 2007 @ 19:27:
[...]

Een subquery is niet inherent sneller dan een join, maar leest vaak wel iets eenvoudiger. Vaak is het zelfs beter om iets met een join te doen ipv een subquery, indien mogelijk.

Een join is alleen sneller indien de relatie m:1/1:1 is. Indien de relatie 1:n is, is een subquery veelal sneller, want je zit niet met duplicates.

Ik zie in deze thread veel 'raad' voorbij komen die louter van toepassing is op achterhaalde engines als mysql, waar 'join' helemaal uitgeoptimaliseerd is en subqueries maar half en brak zijn geimplementeerd. Bij een normale database engine staan de zaken veelal anders.

[ Voor 36% gewijzigd door EfBe op 28-11-2007 20:37 ]

[Cascade]

Verwijderd schreef op woensdag 28 november 2007 @ 20:17:
[...]
Zal waarschijnlijk prima werken bij MySQL, maar zo ongeveer iedere andere database zal 'm niet accepteren: wanneer je GROUP BY gebruikt moeten volgens de SQL regels alle velden die niet in de GROUP BY zitten geaggregeerd zijn, en families.don en don.naam zijn dat hier niet.
...

Klopt. Grappig dat je dit zegt, want ik dacht er wel aan hoe het mogelijk was dat ik die foutmelding nooit heb gezien bij MySQL maar wel bij Access. MySQL staat die query inderdaad gewoon toe, in de standaard out-of-the-box configuratie.

Je kan GROUP BY in MySQL strenger maken door de optie ONLY_FULL_GROUP_BY aan te zetten, maar de partial GROUP BYs geven wel wat voordelen in MySQL.

Meer info (en waarschijnlijk gekleurde uitleg) in dit artikel: MySQL - Debunking GROUP BY Myths

[ Voor 6% gewijzigd door Cascade op 28-11-2007 21:23 ]

[Voutloos]

EfBe schreef op woensdag 28 november 2007 @ 20:34:
[...]
Waarom is die scalar subquery in de projection grote onzin? Net of jouw benadering met groupby altijd kan. een scalar subquery kan altijd, een groupby alleen in sommige gevallen, of je moet mysql gebruiken en dan kan groupby bizarre vormen aannemen.

Ts gebruikt mysql, en in zijn laatst genoemde query worden alleen maar velden van familie of uitkomsten van aggregate functions/subqueries geselecteerd. Een group by op die familievelden levert helemaal geen bizarre group by op.

En uiteraard zijn er hele stoere dbms'en waarbij die count subquery eenzelfde executionplan zou opleveren, maar bij mysql dus gewoon (helaas) niet. Maar goed, binnen mysql context en met verwijzing naar die exacte subquery moet ik toch wel gewoon kunnen aangeven dat die subquery onzin is?

Zijn query werkt prima, maar als ts

Verwijderd schreef op woensdag 28 november 2007 @ 19:14:
...is een snellere oplossing denk ik dan 1 met 2 joins

zegt, wat een performance opmerking is, is mijn performance gerelateerde reactie ook gewoon in mysql context. Ik kan die query wel voor elke dbms gaan uitleggen/optimaliseren, maar dat slaat ook als een tang op een varken.

EfBe schreef op woensdag 28 november 2007 @ 20:34:
achterhaalde engines als mysql, waar 'join' helemaal uitgeoptimaliseerd is en subqueries maar half en brak zijn geimplementeerd.

Hier geef ik je uiteraard gelijk in, op dat vlak kunnen genoeg zaken verbeterd worden.

Cascade schreef op woensdag 28 november 2007 @ 21:00:
Je kan GROUP BY in MySQL strenger maken door de optie ONLY_FULL_GROUP_BY aan te zetten, maar de partial GROUP BYs geven wel wat voordelen in MySQL.

En er zijn nog wel een paar opties voor stricter gedrag, welke heel nuttig kunnen zijn.