[mysql] set var in select gebaseerd op kolom in left join

[Verwijderd]

Hi leute,

Ik zit met een query waar ik maar niet uit kom.
Het is gebaseerd op het volgende:

Stel ik heb in de database een tabel met facturen en een tabel met factuurregels.
In een overzicht van facturen wil ik alle facturen laten zien met het aantal factuurregels per factuur.

PHP:

SELECT `factuur`.`factuur_id`, `factuur`.`factuur_titel`, COUNT(*) AS `factuurregel_aantal`  FROM `factuurregel` LEFT JOIN `factuurregel` ON factuurregel. factuurregel_factuur_id = `factuur`.factuur_id WHERE (factuur_account_id = 1) GROUP BY factuurregel. factuurregel_id`

Dit resulteert in:

factuur id	factuur titel	aantal factuurregels per factuur
1	test	3
2	test2	6

De factuur regels kunnen echter een bepaalde status 'geaccordeerd' hebben.
Als bij de factuur een van de onderliggende regels niet geaccordeerd is wil ik dat weergeven.

factuur id	factuur titel	aantal factuurregels per factuur	onderliggende factuurregels geaccordeerd
1	test	3	nee
2	test2	6	ja

Feitelijk wil ik in mijn query een extra var 'alle_factuurregels_geaccoordeerd' hebben die afhankelijk is van de kolommen bij de facturen.
Google heeft me nog niet echt veel verder gebracht helaas.
Ik weet wel dat je met variabelen kan werken maar alle gevonden voorbeelden sluiten niet aan bij mijn wens.

Ik begin te twijfelen of dit met 1 query op te lossen is.
Mijn alternatief is dat ik eerst de facturen ophaal en dan per factuur de regels erbij zoek.
Maar dat levert me teveel queries op.

[krvabo]

Zonder te testen verder, en zomaar een idee; Je zou kunnen kijken of het werkt met een MIN() of MAX() op de geaccordeerd-column. Ik weet even niet of die alfabetisch sorteert of werkt met string length, maar een van de twee zou moeten werken. Dus dan 'sorteer' je zo (met de aggregaat functions) dat 'nee' eerst komt. Als 'nee' niet voor komt dan is 'ja' de eerste. Mocht de waarde een enum zijn (en ik hoop dat dat zo is) dan kan ie mogelijk een andere volgorde aanhouden, dus het is dan even testen. Mogelijk heb je daar een 2e group by voor nodig. (group by factuurregel.id, factuurregel.geaccordeerd)

Nogmaals, dit is ongetest en zomaar een idee. Mocht het niet werken dan kun je een subquery overwegen, maar die lijkt me trager. Eventueel zou het ook nog met een extra join op te lossen moeten zijn, maar dat lijkt me ook niet sneller.

[Biersteker]

Of je handelt dit gewoon met een aantal array megres/forloops op. (afhankelijk van de verwachte load).
Kennelijk maar 1 DB.

[ Voor 12% gewijzigd door Biersteker op 08-04-2014 00:05 ]

[Gomez12]

Ik snap je query even niet, wat probeer je te doen met die left join op dezelfde tabel zonder alias (oftewel ik vermoed dat of je from fout is of je left join in dit voorbeeld)

Maar in principe zou ik idd of min of max proberen op je factuurregels. Alternatief is een extra left join op je factuurregels met een extra conditie ('geaccodeerd' =true) en dan daarop een count zodat je weet hoeveel regels geaccodeerd zijn, dan kan je met een if bepalen of je aantal geaccodeerde regels gelijk is aan je aantal regels per factuur.
Door het totaal aantal regels vs het aantal geaccodeerde regels te zetten kan je gelijk ook uitgebreider sorteren door bijv een waarde erbij mee te nemen (iets van 1 euro kan vrij snel geaccodeerd worden, terwijl iets van 1 miljoen wellicht wel het nakijken waard is)

@biersteker : Waarom zou je ooit die richting op willen? De enige reden die ik kan bedenken is dat je 100% zeker weet dat je heel weinig aantallen gaat krijgen (is niet te hopen als het om facturen / factuurregels gaat) en dat het echt een functie is die niet binnen sql te proppen is.

[Verwijderd]

Doe een SUM van het aantal geaccordeerde factuurregels. Als je een 0 hebt voor "nee" en een 1 voor "ja" dan is het simpelweg een sum van die kolom. Maar een SUM op TRUE en FALSE zou ook moeten werken
En dan is het makkelijk. Als die SUM gelijk is aan de COUNT dan is alles geaccordeerd, en anders niet.

[Biersteker]

Gomez12 schreef op dinsdag 08 april 2014 @ 00:07:


@biersteker : Waarom zou je ooit die richting op willen? De enige reden die ik kan bedenken is dat je 100% zeker weet dat je heel weinig aantallen gaat krijgen (is niet te hopen als het om facturen / factuurregels gaat) en dat het echt een functie is die niet binnen sql te proppen is.

Cross Domain MultiDB stuff. Geen idee waarom ik dacht dat dat hier van toepassing was...hoewel als het gaat om dag/week data sets, ach...waarom zou je een sql server irriteren, als je het vrij snel in php (of hack) kan afhandelen.(Data Spugende SQL, beheersbare frontend data bijv.(while loop array append tijdens sql whiles bij niet te breede/lange results))

[ Voor 22% gewijzigd door Biersteker op 08-04-2014 00:42 ]

[krvabo]

Verwijderd schreef op dinsdag 08 april 2014 @ 00:11:
Doe een SUM van het aantal geaccordeerde factuurregels. Als je een 0 hebt voor "nee" en een 1 voor "ja" dan is het simpelweg een sum van die kolom. Maar een SUM op TRUE en FALSE zou ook moeten werken
En dan is het makkelijk. Als die SUM gelijk is aan de COUNT dan is alles geaccordeerd, en anders niet.

Ohh dit zou een heel nette oplossing zijn (mits boolean / tinyint(1)), niet aan gedacht

[Verwijderd]

Man,

hoe simpel kan t af en toe zijn :-)
Oplossing van Cheatah is wat mij betreft de meest eenvoudige!

Thanks allen voor de input!