[SQL/MySQL] Verwijderen van count(kolom = waarde)

[FireDrunk]

Beste Tweakers,

Ik zit met een SQL probleem,

Ik heb een table met acties genaamd actions, deze kunnen gekoppeld zitten aan een project.
De tabel ziet er als volgt uit.

code:

actions: 

actionid AS INT,
projectid AS INT,
done AS BOOLEAN.

code:

projects:

projectid AS INT,
beschrijving AS text

Nu wil ik alle projecten verwijderen waarvan _alle_ actions op done staan.

Ik heb echt al tig opties geprobeerd, maar weet niet waar ik het moet zoeken.

Enige probeersels:

code:

SELECT * FROM projects JOIN actions HAVING count(done) = count(actionid);

1 resultaat, moet 2 zijn

code:

SELECT projectid, COUNT(*), (SELECT count(actionid) FROM actions GROUP BY projectid) AS aantalactions FROM actions WHERE done = 1 GROUP BY projectid;

foutmelding

Heeft er iemand een idee?

[pedorus]

Gebruik bijvoorbeeld not exists (select * from ... where ...) of left join ... on ... and ... where ... is null of

SQL:

not projectId in (select projectId from actions where ...)

Laatste zou ik aanraden.

[ Voor 33% gewijzigd door pedorus op 30-10-2008 17:55 ]

[FireDrunk]

SELECT projectid FROM projects WHERE NOT projectid IN (SELECT projectid, done FROM actions WHERE projectid IS NOT NULL GROUP BY projectid HAVING done = 1);

Operand should contain 1 column

NOT IN mag maar op 1 column en voor HAVING done = 1 moet je perse done meeselecteren.

[RobIII]

Waarom select je "done" in je subquery?

thijs_cramer schreef op donderdag 30 oktober 2008 @ 18:22:
NOT IN mag maar op 1 column en voor HAVING done = 1 moet je perse done meeselecteren.

Who says so?

En .oisyn's oplossing hieronder is natuurlijk nog mooier

[ Voor 82% gewijzigd door RobIII op 30-10-2008 18:29 ]

[.oisyn]

@thijs_cramer: Dat kan helemaal niet, als je groepeert op projectid dan moet je op 'done' een aggregate function toepassen (welke moet ie anders kiezen? Gewoon maar een random rij zoals MySQL foutief doet?)

SQL:

SELECT projectid, COUNT(actionid), SUM(done)
FROM actions
GROUP BY projectid
HAVING COUNT(actionid) = SUM(done)

Nu krijg je alleen projectid's, maar da's voldoende om te verwijderen.

.edit: nee bovenstaande klopt niet helemaal, moment
.edit2: zo. Werkt SUM op een boolean?

[ Voor 31% gewijzigd door .oisyn op 30-10-2008 18:31 ]

[pedorus]

thijs_cramer schreef op donderdag 30 oktober 2008 @ 18:22:
SELECT projectid FROM projects WHERE NOT projectid IN (SELECT projectid, done FROM actions WHERE projectid IS NOT NULL GROUP BY projectid HAVING done = 1);

Operand should contain 1 column

-->

SQL:

SELECT projectid FROM projects WHERE projectid NOT IN 
    (SELECT projectid FROM actions WHERE projectid IS NOT NULL and not done);

(Zie de documentatie? )

Waarschijnlijk iets sneller alternatief, aangezien projectid blijkbaar NULL kan zijn:

SQL:

SELECT projectid FROM projects p WHERE not exists 
    (SELECT 1 FROM actions a WHERE a.projectid=p.projectid and not a.done);

(Ik zie dat IN in MySQL wordt geconverteerd naar EXISTS en dat is lastig met NULLs (vervolg blog)

[FireDrunk]

Dat was em!!! Heel erg bedankt!
Ik dacht er natuurlijk niet aan om een NOT query te maken!
Slimme truuk!

[.oisyn]

Werkte mijn query niet?

[pedorus]

.oisyn schreef op vrijdag 31 oktober 2008 @ 13:23:
Werkte mijn query niet?

Werkt ook lijkt me, enkel dan werken indexen waarschijnlijk niet.. Vandaar dat ik die mogelijkheid niet aanraadt

En dan is er nog de mogelijkheid om dingen als NULL (met exists gezien als true) en 4 (met exists gezien als true) in een BOOLEAN te stoppen, want MySQL doet nog niet aan echte booleans.

[KabouterSuper]

.oisyn's oplossing lijkt me beter....NOT EXISTS-operatoren zijn in de praktijk best duur.

[curry684]

.oisyn schreef op donderdag 30 oktober 2008 @ 18:27:
.edit2: zo. Werkt SUM op een boolean?

In MySQL iig wel, daar is een BOOL gewoon alias voor int(1).

[.oisyn]

pedorus schreef op vrijdag 31 oktober 2008 @ 13:53:
[...]

Werkt ook lijkt me, enkel dan werken indexen waarschijnlijk niet..

Ik zie niet in waarom niet. Sterker nog, volgoens mij zijn de gebruikte indices in mijn query effectiever dan die in jouw query.

[pedorus]

KabouterSuper schreef op vrijdag 31 oktober 2008 @ 13:58:
.oisyn's oplossing lijkt me beter....NOT EXISTS-operatoren zijn in de praktijk best duur.

Het lijkt me zeer sterk dat een index gebruiken duurder is dan alle records afgaan per groep en optellen.

Maar als het dan toch moet zou de mogelijkheid van actionID=NULL ook uitsluiten, en die met true!=1 ook. Dus voor een faire vergelijking zou ik het volgende doen:

SQL:

HAVING COUNT(*) = SUM(done!=FALSE)

(wel andere werking als done NULL is, dat wordt nu als FALSE gezien)

.oisyn schreef op vrijdag 31 oktober 2008 @ 14:08:
Ik zie niet in waarom niet. Sterker nog, volgoens mij zijn de gebruikte indices in mijn query effectiever dan die in jouw query.

Probeer het uit Ik heb het over indexen op done, die in jouw geval niet gebruikt kunnen worden.

[ Voor 22% gewijzigd door pedorus op 31-10-2008 14:13 ]

[KabouterSuper]

pedorus schreef op vrijdag 31 oktober 2008 @ 14:10:
[...]

Het lijkt me zeer sterk dat een index gebruiken duurder is dan alle records afgaan, groeperen en optellen.

Hangt ervan af hoe vaak je de subquery uitvoert....je zult toch wel alle records van ACTIONS aflopen, dus ik betwijfel of je index je erg veel helpt.

[pedorus]

KabouterSuper schreef op vrijdag 31 oktober 2008 @ 14:15:
[...]


Hangt ervan af hoe vaak je de subquery uitvoert....je zult toch wel alle records van ACTIONS aflopen, dus ik betwijfel of je index je erg veel helpt.

Nee juist niet, met de juiste optimalisatie loop je alleen de records van action af die done=0 hebben, volgens de (gecombineerde) index op done. Met een gecombineerde index hoef je zelfs niet in de daadwerkelijke records te kijken. EXISTS is ook geen subquery die steeds opnieuw wordt geëvalueerd, maar heeft meer weg van een join-type (zie bijv. die blog-post die ik aanhaalde.)

Maar ik heb geen uitgebreide tests met MySQL gedaan, dus als je het zeker wil weten, moet je het even testen...