[mysql] oplossing query probleem zonder subqueries

[marty]

Als ik de volgende query heb:

MySQL:

SELECT COUNT(id) AS total
FROM A, B
WHERE
    (
        (A.veld1='een' OR A.veld1='twee')
            ||
        (B.veld2='a' OR B.veld2='b')
    )
AND A.id = B.a_id
GROUP BY A.veld3

tabel A heeft als primary key id en in tabel B staan vaak meerdere rows die naar zo'n id verwijzen. Het probleem als ik die join is dat ik dus meerdere rows per id kan terugkrijgen. Deze query werkt daarom niet, want wat ik wil is tellen hoeveel keer er een id voorkomt waarbij die selectie criteria voldaan worden. Eigenlijk zou ik dus eerst moeten kunnen groeperen op id, en dan pas op veld3 waarbij ik die COUNT() laat gelden. Maar ik zie niet hoe ik dat zou kunnen doen zonder subqueries. Iemand een idee?

[whoami]

code:

SELECT count(b.id), b.id
from a, b
where a.pk = b.id
group by b.id

basic SQL kennis.

Je kan misschien eens een SQL tutorial doornemen.

[ Voor 34% gewijzigd door whoami op 18-11-2003 12:31 ]

[marty]

whoami schreef op 18 november 2003 @ 12:30:
basic SQL kennis.

met dat verschil dat je nu alleen stap 1 heb voorgedaan (waar ik het over eerst heb) en ik al heb aangegeven dat er dus meer aan de hand is

Magoed, zal wel niet duidelijk geweest zijn.
Meer voorbeelden:

code:

tabel A
id  naam    textveld    leeftijd
1   jan   [text]        18
2   piet      [text]        19
3   klaas    [text]     18

tabel B
id  a_id    textveld
1   1       [text]
2   1       [text]
3   2       [text]
4   2       [text]
5   2       [text]
6   3       [text]

als search-criteria zoek ik nu naar een aantal woorden die in het textveld van A moeten voorkomen en naar een aantal woorden die in textveld van B moeten voorkomen en daarbij wil ik weten om hoeveel personen het per leeftijdsgroep gaat.

misschien denk ik wel krom hoor, maar ik zie niet in hoe ik dit zonder subqueries kan oplossen. Tja...een temporary tabel zou nog een oplossing zijn, maar ik hoopte eigenlijk dat het ook zonder dat kon

[whoami]

Die search-criteria kan je dan toch gewoon in je WHERE kwijt? Of mis ik iets?

[marty]

de echte query is een stuk groter, maar ik probeerde alleen een versimpelde weergave te geven. die search-criteria hebben er eigenlijk ook niet zoveel mee te maken.
Maar...(het is laat geworden gisternacht, dus excuus als ik van niets een probleem maak), nogmaals:

in deze versimpelde weergave wil ik het aantal personen per leeftijdsgroep weten. normaal zou je dan gewoon doen:
GROUP BY tabel.leeftijd
echter, doordat ik informatie van een tweede tabel op het geheel JOIN kan 1 persoon meerdere keren in de resultaten voorkomen. Als ik dan simpelweg GROUP BY leeftijd zou doen, dan krijg ik dus een verstoord beeld. Normaal zou je die dubbele records er uit filteren door een GROUP BY id te doen (zoals je zelf ook al aangaf), echter dan heeft het geen zin meer om op leeftijd te groeperen, omdat je ook al onderscheid maakt tussen verschillende id's en dat zal dus geen effect hebben.
dus eigenlijk zou je éérst op id moeten kunnen groeperen en dáárna pas op leeftijd icm een COUNT()

[whoami]

Waar maak jij onderscheid (waar groepeer je) op ID?

In je group by list hoef je enkel de velden op te nemen die in je select list staan, en geen aggregated functie zijn. Als je ID dus niet in je select-list opneemt, hoef je (mag je) daar ook niet op grouperen.

[marty]

whoami schreef op 18 november 2003 @ 13:43:
Waar maak jij onderscheid (waar groepeer je) op ID?

Nog nergens aangezien ik nog aan het zoeken ben naar een goede oplossing

Maar ik heb net een helder moment gekregen. Ik kan het gewoon oplossen door m'n id-veld te DISTINCTen , dan kan ik ongestoord op leeftijd groeperen zonder bepaalde records dubbel te tellen.

het (versimpeld) resultaat is dus nu:

MySQL:

SELECT DISTINCT A.id, COUNT(A.id) AS total, A.leeftijd
FROM A, B
WHERE
    /* search criteria */
AND A.id = B.a_id
GROUP BY A.leeftijd
ORDER BY A.leeftijd

evengoed bedankt

[whoami]

DISTINCT werkt op de ganse row, en niet op één veld alleen binnen die row.
Trouwens, waarom wil je daar het ID nog bijhebben in je select list? Welk id wil je dan zien?

Stel je hebt deze records:

code:

id     leeftijd
1      18
2      18
3      19

Stel dat je nu wilt weten hoeveel records per leeftijd je hebt, dan doe je dit:

code:

select leeftijd, count(leeftijd) from tabel group by leeftijd

en dan krijg je:

code:

   18     2
   19    1

Geen probleem.
Echter, nu wil jij het id er ook bij zetten.... Welk ID wil je er dan wel bij voor het record dat de info ivm leeftijdsgroep 18 weergeeft?

[marty]

ik ben nog steeds niet helemaal wakker. dit was inderdaad geen oplossing.

oké, terug naar het probleem dus:
schrijf 'm nog maar een keertje op: ik wil van de resultaten van m'n query tellen hoevaak een record (id) per leeftijdsgroep voorkomt, waarbij ik alleen unieke id's tel (en het probleem is dus dat een id meerdere keren kan voorkomen). hierbij kan ik geen gebruik maken van subqueries en lijkt een temporary tabel me niet handig en wil ik dus liever vermijden.

[whoami]

dan zie ik het probleem niet in met de query van m'n eerste post in dit topic.

code:

select tabel2.id, count(tabel2.id)
from tabel2, tabel1
where tabel2.id = tabel1.pk
and tabel1.veld = "blaat" or tabel2.veld = "blaat"
group by tabel2.id

[marty]

whoami schreef op 18 november 2003 @ 14:56:
dan zie ik het probleem niet in met de query van m'n eerste post in dit topic.

code:

select tabel2.id, count(tabel2.id)
from tabel2, tabel1
where tabel2.id = tabel1.pk
and tabel1.veld = "blaat" or tabel2.veld = "blaat"
group by tabel2.id

omdat ik het aantal personen per leeftijdsgroep wil weten en dat doet deze query totaal niet.
(groeperen op tabel2.id heeft zowiezo geen zin omdat dat die uniek zijn en er dus als totaal overal 1 zou uitkomen)

Bekijk m'n 2e post nog eens voor het (versimpelde) voorbeeld van de tabellen en die post er na voor het probleem, want volgens mij leg ik dat daar toch vrij duidelijk uit

[whoami]

Nee, ik haal id uit tabel2, waar dus meerdere keren het zelfde id (foreign key) kan inzitten.

Nu snap ik het volledig: je wilt enkel het aantal unieke id's tellen per leeftijdsgroep.

Kan je het dan niet zo doen:

code:

select tabel1.leeftijd, tabel2.id, count(tabel2.id)
from tabel1, tabel2
where tabel1.id = tabel2.id
group by leeftijd, id

Dan heb je wel nog niet de gegevens zoals ik vermoed dat jij zou willen. Je krijgt dan nl per leeftijd en per id het aantal x dat dat id voorkomt in tabel2.
Ik ben nu wel niet zo bekend met MySQL, dus hoe je het dan best in MySQL oplost weet ik niet.
In Sql Server heb je nog een 'with rollup' clausule die per group nog eens subtotalen maakt, maar dat zal in mysql wel niet bestaan.

[marty]

whoami schreef op 18 november 2003 @ 15:30:
Nee, ik haal id uit tabel2, waar dus meerdere keren het zelfde id (foreign key) kan inzitten.

Nu snap ik het volledig: je wilt enkel het aantal unieke id's tellen per leeftijdsgroep.

yes van tabel1 / 'A in het voorbeeld' wel te verstaan

Kan je het dan niet zo doen:

code:

select tabel1.leeftijd, tabel2.id, count(tabel2.id)
from tabel1, tabel2
where tabel1.id = tabel2.id
group by leeftijd, id

Dan heb je wel nog niet de gegevens zoals ik vermoed dat jij zou willen. Je krijgt dan nl per leeftijd en per id het aantal x dat dat id voorkomt in tabel2.

als je tabel2.id gebruikt, voortbordurend op mijn eerder gegeven voorbeeld van hoe de tabellen er uitzien dan is dit een uniek nummer. Daarop groeperen heeft dus zowiezo geen zin. En op de id van tabel1 groeperen heeft uiteraard ook geen zin. (ik ben dus alleen geinteresseerd in totalen per leeftijd, dus die moeten niet nog eens verspreid staan)
Dit biedt dus helaas ook geen oplossing.

In Sql Server heb je nog een 'with rollup' clausule die per group nog eens subtotalen maakt, maar dat zal in mysql wel niet bestaan.

heb er niets over kunnen vinden .....

[whoami]

Ik bedoel dan ook a_id uit tabel 2 (de foreign key dus , zoals ik al aangaf).

[marty]

whoami schreef op 18 november 2003 @ 16:31:
Ik bedoel dan ook a_id uit tabel 2 (de foreign key dus , zoals ik al aangaf).

nee, ik weet niet hoe dit in andere sql-talen werkt, maar dan groepeert mysql hetzelfde als wanneer je zou groeperen op de primary key van tabel 1 (wat ik overigens ook logisch vindt, aangezien je 'm daar op joined). en dan is de leeftijd dus alsnog verspreid over verschilende id's en krijg ik bovendien een verkeerd totaal (met COUNT). Dan zou ik er dus nog meer bij gebaat zijn helemaal geen totaal te berekenen en alleen daar op te groeperen en daarna met PHP ofzo maar verder te groeperen op leeftijd