[SQL] Dubbele Count

[Jaspertje]

Het volgende probleem: Ik heb een tabel met relaties en een koppeltabel tussen relaties en componenten. Deze twee tabellen gaat het om.

Er ligt ook een relatie tussen de tabel relatie en dezelfde tabel relatie. Omdat een andere relatie kan erven van de 1e relatie Bijvoorbeeld (ff voorbeeld).
de tabel relatie:

relatieid	relatiecode	erftvan
1	jasper	Null
2	test	Null
3	GoT	2
4	Tweakers	Null
5	temp	2
6	bla	1

de tabel relatie_componenten

RelatieID	ComponentID
1	1
1	2
1	3
2	1
2	5
4	6
1	4
1	5
4	6

Nu wilde ik graag met een query het volgende weten:
- Alle relatiecodes die nier erfven (Where ErftVan Is Null)
- Het aantal childs die wel erven bij dat relatieID
- Het aantal componenten dat de relatie heeft.

Moet te doen zijn lijkt mij. Makkelijk beginnen alleen de erven erbij zetten:

SQL:

select p.relatiecode, count(c.relatieid) As Aantal_Sites from (relatie p Left Join 
relatie c On p.RelatieID = c.ErftVan) where p.erftvan Is Null Group By p.RelatieCode

Dit gaat allemaal goed en wilt ook wel.. Nu dan de componenten erbij... Nou dat wilt dus helemaal niet

SQL:

select p.relatiecode, count(c.relatieid) As Aantal_Sites, count(componentID) from 
(relatie p Left Join relatie c On p.RelatieID = c.ErftVan) left Outer Join 
Relatie_Componenten On p.RelatieID = Relatie_Componenten.RelatieID where p.erftvan Is Null Group By p.RelatieCode

Als ik 16 componenten heb, dan doet ie dat keer het aantal erfven (dus al ik 6 erven heb maakt ie er 96 van) En dat wil ik niet

De vraag, wat zie ik over het hoofd, kan ik wel 2 counts op verschillende tabellen doen in dezelfde query op deze manier? Of bou ik mijn query gewoon verkeerd op?

[jvdmeer]

Getest onde MS-SQL (weet dus niet hoe PG er mee omgaat) :

SQL:

SELECT R.Relatieid, R.Relatiecode, p.aantal As Aantal_Sites, q.Aantal as AantalComponenten
  FROM Relatie R
  LEFT JOIN 
  (
    SELECT erftvan, count(*) as Aantal 
      FROM Relatie
      WHERE ErftVan IS NOT NULL
      GROUP BY erftvan
  ) p
    ON R.Relatieid=P.Erftvan
  LEFT JOIN 
  (
    SELECT relatieID, count(*) as Aantal 
      FROM Relatie_Componenten
      GROUP BY relatieID
  ) q
    ON R.Relatieid=q.RelatieID
  WHERE R.Erftvan IS NULL

De query van regel 5..8 telt de eerste telling, en de query van regel 13..15 telt de tweede telling bij elkaar. Het resultaat wordt dan met de oorspronkelijke tabel gejoined.

Het resultaat:

code:

Relatieid   Relatiecode Aantal_Sites AantalComponenten 
----------- ----------- ------------ ----------------- 
1           jasper      1            5
2           test        2            2
4           Tweakers    NULL         2

PS: ik heb misschien niet gekozen voor de kortste oplossing, maar wel voor een duidelijke en zichzelf documenterende oplossing. En dat vind ik belangrijker.

[ Voor 8% gewijzigd door jvdmeer op 11-11-2003 18:57 ]

[Jaspertje]

Mijn dank is groot jvdmeer.. denk niet dat ik er zelf zo achter was gekomen..