[SQL] "Not Exists" als aggregate

[GrimaceODespair]

ID	Prop
1	A
1	B
1	<null>
2	A
3	A
3	B

Ik heb bovenstaande tabel, en ik zou graag alle unieke id's hieruit filteren die geen null-waardes hebben in hun Prop-kolom. Dat zijn hier dus 2 en 3.

Het liefst zou ik dit klaarspelen zonder subqueries, maar kan dat uberhaupt?

Als het zou bestaan, zou ik iets doen als

SQL:

select *
from T
group by ID
having not exists (Prop is null)

Is het eigenlijk ongelofelijk gemakkelijk en zie ik iets over het hoofd, of kan wat ik wil niet zonder subqueries?

[ACM]

zoiets?

SQL:

select distinct ID
from T t1
where not exists (select * from T t2 where t2.ID =  t1.ID and prop is null)

of heel vies zoiets:

SQL:

select ID
from T
group by ID
having count(*) = sum(case when Prop is null then 0 else 1 end)

Ik snap iig niet wat er tegen subqueries zou zijn?

[ Voor 11% gewijzigd door ACM op 07-11-2006 16:51 ]

[P_de_B]

SQL:

SELECT DISTINCT P1.id
FROM PropTable p1
LEFT OUTER JOIN #PropTable p2 ON P1.Id = P2.Id AND P2.Prop IS NULL
WHERE P2.Id IS NULL

Je JOINed de tabel met zichzelf waarbij je expliciet ook de voorwaarde opneemt dat Prop NULL is, alle records die niet aan deze join criteria voldoen heb jij nodig.

Volgens mij moet dit werken

[GrimaceODespair]

ACM schreef op dinsdag 07 november 2006 @ 16:48:
Ik snap iig niet wat er tegen subqueries zou zijn?

Nou, op zich is er niets tegen subqueries, maar de select waar ik tegenaan kijk is het resultaat van een redelijk omvangrijke join. Ik kan het in een tijdelijke tabel gaan stoppen, of het in een subquery nog eens uitvoeren, maar als het niet hoeft, liever niet dus.

Maar los daarvan bedacht ik me net het volgende:

SQL:

select * 
from T 
group by ID 
having count(Prop) = count(*)

[JeroenTheStig]

edit

whoops, verkeerd gelezen

[ Voor 76% gewijzigd door JeroenTheStig op 07-11-2006 16:57 ]

[Verwijderd]

Het is natuurlijk onzin om dit niet via een subquery te doen, maar goed het kan wel:

SQL:

create table #temp (i int , b varchar(200))
go

insert #temp values (1,  'A')
insert #temp values (1,  'B')
insert #temp values (1,  null)
insert #temp values (2,  'A')
insert #temp values (3,  'A')
insert #temp values (3,  'B')

SELECT * FROM #temp

SELECT DISTINCT t1.i
  FROM #temp t1
  LEFT JOIN (
                SELECT DISTINCT t1.i
                  FROM #temp t1
                 INNER JOIN #temp t2
                    ON t1.i = t2.i
                 WHERE t2.b IS NULL
            ) t2
    ON t1.i = t2.i
 WHERE t2.i is null

DROP  table #temp

[GrimaceODespair]

GrimaceODespair schreef op dinsdag 07 november 2006 @ 16:55:
[...]
Maar los daarvan bedacht ik me net het volgende:

SQL:

select * 
from T 
group by ID 
having count(Prop) = count(*)

Wat natuurlijk niet werkt, want Prop komt niet voor in de Group By...

zie oisyn hieronder...

[ Voor 4% gewijzigd door GrimaceODespair op 07-11-2006 17:17 ]

[.oisyn]

SQL:

select *
from T
group by ID
having sum(Prop is null) = 0

GrimaceODespair schreef op dinsdag 07 november 2006 @ 17:04:
[...]

Wat natuurlijk niet werkt, want Prop komt niet voor in de Group By...

Je gebruikt ook niet Prop maar een aggregate functie die op Prop werkt, wat uiteraard wel mag

[ Voor 131% gewijzigd door .oisyn op 07-11-2006 17:16 ]

[JKVA]

Is dit niet een mooi geval voor een EXCEPT? Of MINOR voor Oraclezen?

Iets van:

SQL:

SELECT id
FROM t
GROUP BY id
EXCEPT
SELECT id
FROM t
WHERE prop IS NULL

(Even uit het blote hoofd ingeklopt)

[GrimaceODespair]

.oisyn schreef op dinsdag 07 november 2006 @ 17:09:

SQL:

select *
from T
group by ID
having sum(Prop is null) = 0

[...]

Sinds wanneer mag je alleen kolommen in de HAVING clause gebruiken die in de GROUP BY clause voorkomen . Volgens mij ben je in de war met de regel dat je alleen kolommen in de SELECT clause mag gebruiken die in de GROUP BY voorkomen, tenzij je er een aggregate functie op los laat.

Je hebt natuurlijk helemaal gelijk Problem solved

[momania]

JKVA schreef op dinsdag 07 november 2006 @ 17:13:
Is dit niet een mooi geval voor een EXCEPT? Of MINOR voor Oraclezen?

Meer iets voor een decode denk ik

SQL:

select ID
from T
group by ID
having sum(decode(Prop, NULL, 1, 0)) = 0

[GrimaceODespair]

Het gaat hier overigens over T-SQL, maar het liefst gebruik ik natuurlijk ANSI-SQL.