[SQL] Subquery met COUNT in SELECT clause

[NMe]

Ik heb voor een simpel zoekscriptje wat code geschreven die de volgende query genereert in mijn testomgeving:

SQL:

SELECT TOP 25 b.onderwerp_id, o.titel, (
     SELECT COUNT(*)
     FROM bericht
     WHERE onderwerp_id = o.id
) AS aantal
FROM onderwerp o, bericht b
WHERE o.id IN (
     SELECT TOP 3 id
     FROM onderwerp
     ORDER BY id ASC
) AND (
     o.id IN (
          SELECT onderwerp_id
          FROM bericht
          WHERE post LIKE '%test%'
     ) OR o.id IN (
          SELECT id
          FROM onderwerp
          WHERE titel LIKE '%test%'
     )
) AND b.onderwerp_id = o.id
GROUP BY aantal, b.onderwerp_id, o.id, o.titel
ORDER BY b.onderwerp_id DESC

Deze query ziet er nodeloos ingewikkeld uit, maar dat is nodig om de paginaindeling te laten werken. Beide SELECTS met TOP erin zijn nodig om waardes midden uit de tabel op te halen, zonder overbodige records.

Het probleem zit hem in de COUNT in de eerste subquery. Access ziet dit niet als functie, en als ik de query wil uitvoeren wordt me gevraagd een waarde in te vullen voor de 'variabele' Count. Nou zal dat vast om de een of andere reden logisch zijn, maar ik vat hem niet.

Heb het afgelopen uur op sqlcourse (1 en 2) rondgehangen, maar ik kan vinden hoe ik dit op kan lossen, alles wat ik geprobeerd heb leverde meer of andere foutmeldingen op...

Kan iemand me aanwijzen waar mijn denkfout zit?

[P_de_B]

Ondersteunt Access wel correlated subqueries?

[NMe]

Volgens mij wel, ik heb ooit iets dergelijks gebruikt, kan alleen de source daarvan niet meer vinden om te spieken...

Edit: Mijn query werkt zonder die COUNT-subquery perfect, en geeft me precies de goede waardes terug, maar ik heb ook dat aantal nodig... Wil het toch liever bij één recordset houden.

[ Voor 45% gewijzigd door NMe op 18-05-2004 13:27 ]

[cameodski]

Je kunt anders nog eens proberen of de count constructie in de table list wel werkt.
Dan moet je dus zoiets toevoegen:

code:

INNER JOIN (
 SELECT COUNT (*) AS aantal, onderwerp_id
 FROM bericht
 GROUP BY onderwerp_id
 ) AS t ON (t.onderwerp_id = o.id)

[whoami]

Ik denk dat Access een probleem heeft met de combinatie TOP en COUNT.
Ik vraag me zelfs af of SQL Server dat wel toelaat.

[ Voor 28% gewijzigd door whoami op 18-05-2004 13:48 ]

[NMe]

cameodski schreef op 18 mei 2004 @ 13:39:
Je kunt anders nog eens proberen of de count constructie in de table list wel werkt.
Dan moet je dus zoiets toevoegen:

code:

INNER JOIN (
 SELECT COUNT (*) AS aantal, onderwerp_id
 FROM bericht
 GROUP BY onderwerp_id
 ) AS t ON (t.onderwerp_id = o.id)

Dat kan niet werken, een count geeft een integer terug, geen tabel. Joins kunnen alleen met tabellen toch?

[P_de_B]

NMe84 schreef op 18 mei 2004 @ 13:49:
[...]

Dat kan niet werken, een count geeft een integer terug, geen tabel. Joins kunnen alleen met tabellen toch?

Dit is een zgn derived table. Je kunt gewoon de count() kolom in je select list opnemen.
De naam van de tabel is in het voorbeeld 't'.

Dus:

SELECT *, t.Aantal
FROM [...]

[ Voor 14% gewijzigd door P_de_B op 18-05-2004 13:51 ]

[NMe]

whoami schreef op 18 mei 2004 @ 13:48:
Ik denk dat Access een probleem heeft met de combinatie TOP en COUNT.
Ik vraag me zelfs af of SQL Server dat wel toelaat.

Daar heb ik eerder nog nooit problemen mee gehad.

@P_de_B: dat levert me een syntax error op, maar ik zal wel wat verkeerd doen.

Ik heb nu deze query geprobeerd:

SQL:

SELECT TOP 25 b.onderwerp_id, o.titel, (
      SELECT MAX(plaatsingstijd)
      FROM bericht
      HAVING onderwerp_id = o.id
) AS laatstepost, (
     SELECT COUNT(*)
      FROM bericht
      HAVING onderwerp_id = o.id
) AS aantal
FROM onderwerp o, bericht b
WHERE o.id IN (
      SELECT TOP 1 id
      FROM onderwerp
      ORDER BY id ASC
) AND (
      o.id IN (
            SELECT onderwerp_id
            FROM bericht
            WHERE post LIKE '%test%'
      ) OR o.id IN (
            SELECT id
            FROM onderwerp
            WHERE titel LIKE '%test%'
      )
) AND b.onderwerp_id = o.id
GROUP BY b.onderwerp_id, o.id, o.titel
ORDER BY b.onderwerp_id DESC

Zo execute de query wel, maar aantal bevat nu een NULL waarde, en laatstepost ook vermoed ik...

[ Voor 64% gewijzigd door NMe op 18-05-2004 14:00 ]

[curry684]

whoami schreef op 18 mei 2004 @ 13:48:
Ik denk dat Access een probleem heeft met de combinatie TOP en COUNT.
Ik vraag me zelfs af of SQL Server dat wel toelaat.

Volgens SQL Server is z'n query grammaticaal 100% correct. Ik zou ook niet weten waarom dat niet zou mogen?

[cameodski]

Ik vind je query er nogal excentriek uitzien. Een aantal vragen/opmerkingen:
1. Waarom doe je in je hoofdquery een group by?
2. Je hebt oa deze filter:

code:

) AND (
      o.id IN (
            SELECT onderwerp_id
            FROM bericht
            WHERE post LIKE '%test%'
      ) OR o.id IN (
            SELECT id
            FROM onderwerp
            WHERE titel LIKE '%test%'
      )
)

Waarom niet het volgende (let op de haakjes):

code:

AND (bericht.post LIKE '%test%' OR onderwerp.titel LIKE '%test%')

3. Probeer dit stukje query eens te verwerken in jouw query:

code:

SELECT TOP 25 b.onderwerp_id, o.titel, t.aantal, t.laatste
FROM onderwerp o
INNER JOIN bericht b ON (b.onderwerp_id = o.id)
INNER JOIN (
 SELECT COUNT (*) AS aantal, MAX (plaatsingstijd) AS laatste, onderwerp_id
 FROM bericht
 GROUP BY onderwerp_id
 ) AS t ON (t.onderwerp_id = o.id)

[ Voor 5% gewijzigd door cameodski op 18-05-2004 14:23 ]

[NMe]

Het vreemde is trouwens dat Access met die laatste query geen records teruggeeft, en ASP wel, maar dan met null-waardes voor aantal en laatstepost...

[NMe]

cameodski schreef op 18 mei 2004 @ 14:23:
Ik vind je query er nogal excentriek uitzien. Een aantal vragen/opmerkingen:
1. Waarom doe je in je hoofdquery een group by?

Goeie vraag.

cameodski schreef op 18 mei 2004 @ 14:23:
2. Je hebt oa deze filter:

code:

*snip*

Waarom niet het volgende (let op de haakjes):

code:

AND (bericht.post LIKE '%test%' OR onderwerp.titel LIKE '%test%')

Dat had ik eerst, maar kreeg ik niet goed werkend, dus heb ik het op die vage manier herschreven.

cameodski schreef op 18 mei 2004 @ 14:23:
3. Probeer dit stukje query eens te verwerken in jouw query:

code:

*snip*

Ik heb je opmerkingen verwerkt in een nieuwe query, die er nu zo uit ziet:

SQL:

SELECT TOP 25 b.onderwerp_id, o.titel, t.aantal, t.laatste
FROM onderwerp o
INNER JOIN bericht b ON (b.onderwerp_id = o.id)
INNER JOIN (
      SELECT COUNT (*) AS aantal, MAX (plaatsingstijd) AS laatste, onderwerp_id
      FROM bericht
     GROUP BY onderwerp_id
) AS t ON (t.onderwerp_id = o.id)
WHERE o.id IN (
      SELECT TOP 1 id
      FROM onderwerp
      ORDER BY id ASC
) AND (
      bericht.post LIKE '%test%'
      OR onderwerp.titel LIKE '%test%'
)
ORDER BY b.onderwerp_id DESC

Nou krijg ik echter een syntax error omdat er in het volgende stuk een operator zou ontbreken?

SQL:

(b.onderwerp_id = o.id)
INNER JOIN (
      SELECT COUNT (*) AS aantal, MAX (plaatsingstijd) AS laatste, onderwerp_id
      FROM bericht
     GROUP BY onderwerp_id
) AS t ON (t.onderwerp_id = o.id)

Volgens mij klopt dit gewoon....

[cameodski]

Misschien dat ie nog graag wat keren het woordje AS wil zien als je een alias gebruikt

[NMe]

cameodski schreef op 18 mei 2004 @ 14:48:
Misschien dat ie nog graag wat keren het woordje AS wil zien als je een alias gebruikt

Nope, geen enkel verschil...

[cameodski]

En wat zegt ie als je de join op de derived table helemaal weglaat?

[NMe]

cameodski schreef op 18 mei 2004 @ 14:58:
En wat zegt ie als je de join op de derived table helemaal weglaat?

Dan krijg ik één record terug, dat helemaal leeg is.

[NMe]

Probleem opgelost!

ASP.NET Visual Basic:

    query = "SELECT TOP 25 b.onderwerp_id, o.titel, (" &_
              "SELECT MAX(plaatsingstijd) " &_
              "FROM bericht " &_
              "WHERE onderwerp_id = o.id" &_
            ") AS laatstepost, (" &_
              "SELECT COUNT(*) " &_
              "FROM bericht " &_
              "WHERE onderwerp_id = o.id" &_
            ") AS aantal " &_
            "FROM onderwerp o, bericht b " &_
            "WHERE o.id IN (" &_
              "SELECT TOP " & GetAantal - (Pagina * 25) & " id " &_
              "FROM onderwerp " &_
              "ORDER BY id ASC" &_
            ")  AND (" &_
              "b.post LIKE '%" & Request("q") & "%' " &_
              "OR o.titel LIKE '%" & Request("q") & "%'" &_
            ") AND b.onderwerp_id = o.id " &_
            "GROUP BY onderwerp_id, titel, o.id " &_
            "ORDER BY b.onderwerp_id DESC"

_{Ben ff te lui om de query mooi neer te zetten, dit is ook wel leesbaar.}

edit:
Jaja, ik weet ut, SQL injection.

[ Voor 23% gewijzigd door NMe op 18-05-2004 16:23 ]

[P_de_B]

offtopic:
[edit]Jaja, ik weet ut, SQL injection. :)[/edit][/quote]

Wat is de URL van je site

[NMe]

P_de_B schreef op 18 mei 2004 @ 16:29:

offtopic:
[edit]Jaja, ik weet ut, SQL injection. :)[/edit][/quote]

Wat is de URL van je site

Als dat je gelukkig maakt, http://www.nvknf.nl/nieuwe%20site/JEROEN/forum/search.asp. SQL injection kan alleen niet meer in dit stukkie code.

[cameodski]

En waarom pikt ie de COUNT (*) nu opeens wel

[NMe]

cameodski schreef op 18 mei 2004 @ 17:15:
En waarom pikt ie de COUNT (*) nu opeens wel

Om de een of andere reden mocht in de eerste 2 subqueries geen HAVING staan, maar WHERE... Vraag me niet waarom, want volgens mij moet je nog steeds HAVING gebruiken bij een aggregate functie... Maja, het werkt.

Enige probleem is nog dat ik niet op laatstepost kan sorteren, dan zegt ie dat er een waarde ontbreekt...

[NMe]

Ik krijg het niet voor elkaar om mijn records netjes te sorteren, en ook de verdeling van zoekresultaten over pagina's wil niet lukken.

code:

SELECT TOP 2 b.onderwerp_id, o.titel, (
  SELECT MAX(plaatsingstijd)
  FROM bericht
  WHERE onderwerp_id = o.id
) AS laatstepost, (
  SELECT COUNT(*)
  FROM bericht
  WHERE onderwerp_id = o.id
) AS aantal
FROM onderwerp o, bericht b
WHERE o.id IN (
  SELECT TOP 7 id
  FROM onderwerp
  WHERE b.onderwerp_id = id
  ORDER BY b.plaatsingstijd DESC
)  AND (
  LCASE(b.post) LIKE '%test%'
  OR LCASE(o.titel) LIKE '%test%'
)
GROUP BY b.plaatsingstijd, onderwerp_id, titel, o.id
ORDER BY b.plaatsingstijd ASC

Die SELECT TOP 7 moet aangepast worden als je de volgende pagina wil bekijken, in dit geval wordt ie dan 5. Dat klopt ook, maar ik krijg toch nog steeds dezelfde 2 records terug... Ook het sorteren op laatstepost wil ie niet goed doen, sorteren op MAX(laatstepost) ook niet natuurlijk, en ook b.plaatsingstijd geeft niet het resultaat wat ik wil.

Het gedrag van de queries kun je hier zien. Misschien dat iemand me kan aanwijzen waar ik fout zit? Ik word er in ieder geval krankjorem van.

[NMe]

*Laatste kickje* Als niemand me kan helpen zal ik waarschijnlijk met meerdere queries aan de slag moeten...