[SQL] IN statement werkt niet in LEFT JOIN?

[MarkvE]

Ik ben bezig met een query die in één keer alles ophaalt (zoals de categorieën en de daarbij behorende fora) voor de index van mijn forum. Alleen werkt het IN statement kennelijk niet in een LEFT JOIN, want wanneer ik dat probeer krijg ik een MySQL error, namelijk:

code:

You have an error in your SQL syntax near 'SELECT id FROM actions WHERE name = 'edit' OR name = 'lock' OR name = 'delete') ' at line 29

En dan is dit mijn Query:

SQL:

SELECT
   c.id AS cid,
   c.name AS category,
   f.id AS fid,
   f.name AS forum,
   f.description AS description,
   COUNT(DISTINCT t.id) AS topics,
   COUNT(DISTINCT p.id) - COUNT(DISTINCT t.id) AS posts,
   MAX(p.time) AS last,
   r.user AS mid,
   u.username AS moderator
FROM
   categories AS c
INNER JOIN
   fora AS f
ON
   f.category = c.id
LEFT JOIN
   topics AS t
ON
   t.forum = f.id
LEFT JOIN
   posts AS p
ON
   p.topic = t.id
LEFT JOIN
   rights AS r
ON
   r.forum = f.id AND r.action IN (SELECT id FROM actions WHERE name = 'edit' OR name = 'lock' OR name = 'delete')
LEFT JOIN
   users AS u
ON
   u.id = r.user
GROUP BY
   f.id, r.user
ORDER BY
   c.order ASC, f.order ASC, u.username ASC

Weet iemand of dit klopt dat er hier geen "IN" kan worden gebruikt (dus kennelijk alleen in een WHERE clausule)? En of ik dit dan anders zou kunnen oplossen?

Bij voorbaat dank!

[ Voor 26% gewijzigd door MarkvE op 13-05-2004 14:36 ]

[NMe]

Ik denk dat niet IN het probleem is, maar AND... De join doe je op gelijkheid van twee velden, niet op een of andere maffe check. Ik zou het herschrijven naar een WHERE clause.

[MarkvE]

Als ik dit doe:

code:

LEFT JOIN
    right AS r
ON
    r.forum = f.id AND r.action = 1

Werkt het wel, dus een AND werkt hier wel...

[ Voor 16% gewijzigd door MarkvE op 13-05-2004 14:41 ]

[P_de_B]

NMe84 schreef op 13 mei 2004 @ 14:37:
Ik denk dat niet IN het probleem is, maar AND... De join doe je op gelijkheid van twee velden, niet op een of andere maffe check. Ik zou het herschrijven naar een WHERE clause.

Je kunt gerust verschillende criteria in een join opnemen hoor.

Probeer eens blokhaken rond name te zetten

WHERE [name] = 'blah'

[ Voor 10% gewijzigd door P_de_B op 13-05-2004 14:42 ]

[MarkvE]

P_de_B schreef op 13 mei 2004 @ 14:41:
[...]


Je kunt gerust verschillende criteria in een join opnemen hoor.

Probeer eens blokhaken rond name te zetten

WHERE [name] = 'blah'

Geeft nog steeds dezelfde error... name is kennelijk geen reserved word.

[whoami]

Die IN moet niet in je JOIN clause staan, maar in je WHERE clause:

code:

SELECT
....
FROM ...
LEFT JOIN blaat ON ...
WHERE blaat.action IN ('edit', 'lock', ....)

[Verwijderd]

Het gebruik van kolom- en tabelnamen die lijken op reserved words kunnen soms problemen veroorzaken. De parser weet het onderscheidt dan niet meer tussen een naam en een SQL-woord. Bv. NAME, EDIT, ID, DELETE, ACTIONS etc. Ik heb wel eens problemen gehad met de veldnamen IN en FROM!

Dit was geen reactie op whoami. De oplossing van whoami is de juiste.

[ Voor 13% gewijzigd door Verwijderd op 13-05-2004 15:10 . Reden: reacte op mijn post ]

[whoami]

tjiba, die 'edit', 'lock', etc.... staan tussen quotes, dus, dat zijn strings. SQL Server zal dat dus niet als reserved words aanzien.

[Markieman]

Misschien een stomme opmerking, maar gebruik je wel de juiste versie van MySQL?
Subqueries zijn pas vanaf MySQL 4.1 ondersteund.

[P_de_B]

Hmm, er zitten nog meer reserved words in

Kijk hier eens naar.

MySQL is het. Ik dacht echt dat ik MSSQL had gezien

[ Voor 22% gewijzigd door P_de_B op 13-05-2004 15:00 ]

[whoami]

Hmmm, waarom dacht ik dat het ook over ms sql ging?

[MarkvE]

Markieman schreef op 13 mei 2004 @ 14:57:
Misschien een stomme opmerking, maar gebruik je wel de juiste versie van MySQL?
Subqueries zijn pas vanaf MySQL 4.1 ondersteund.

Damn, zitten subqueries pas in die versie? Dan denk ik dat mijn host die nog niet ondersteund...

[ Voor 8% gewijzigd door MarkvE op 13-05-2004 15:03 ]

[MarkvE]

whoami schreef op 13 mei 2004 @ 15:01:
Hmmm, waarom dacht ik dat het ook over ms sql ging?

Sorry, vergeten erbij te zetten dat het om MySQL ging...

Ik heb het nu opgelost, enigzins met een klein omweggetje. Ik heb een extra LEFT JOIN neergezet en via deze LEFT JOIN in de WHERE clausule het IN statement gezet.

Misschien is het wel handig om te weten voor mensen die topic ooit nog eens later lezen. Ik heb het op kunnen lossen door de query te veranderen naar dit:

code:

SELECT
   c.id AS cid,
   c.name AS category,
   f.id AS fid,
   f.name AS forum,
   f.description AS description,
   COUNT(DISTINCT t.id) AS topics,
   COUNT(DISTINCT p.id) - COUNT(DISTINCT t.id) AS posts,
   MAX(p.time) AS last,
   r.user AS mid,
   u.username AS moderator
FROM
   categories AS c
INNER JOIN
   fora AS f
ON
   f.category = c.id
LEFT JOIN
   topics AS t
ON
   t.forum = f.id
LEFT JOIN
  posts AS p
ON
   p.topic = t.id
LEFT JOIN
   rights AS r
ON
   r.forum = f.id
LEFT JOIN
   actions AS a
ON
   a.id = r.action
LEFT JOIN
  users AS u
ON
   u.id = r.user
WHERE
   a.name IN ('edit', 'lock', 'delete')
GROUP BY
   f.id, r.user
ORDER BY
   c.order ASC, f.order ASC, u.username ASC

[ Voor 78% gewijzigd door MarkvE op 13-05-2004 15:12 ]

[cameodski]

Kun je uitleggen waarom de left joins gebruikt?
In je where houd je er bijvoorbeeld geen rekening mee dat bepaalde kolommen door de left join ook NULL kunnen zijn.
Dus: of een aantal left joins kunnen gewone joins worden of je filter moet nog aangepast worden.
Overigens is group by op kolommen die je via een left join erbij krijgt ook heel hard roepen om problemen.

[MarkvE]

cameodski schreef op 13 mei 2004 @ 16:32:
Kun je uitleggen waarom de left joins gebruikt?
In je where houd je er bijvoorbeeld geen rekening mee dat bepaalde kolommen door de left join ook NULL kunnen zijn.
Dus: of een aantal left joins kunnen gewone joins worden of je filter moet nog aangepast worden.

Ik heb het nu ook zo aangepast dat er geen WHERE meer nodig is. Dus dat is iig opgelost, want ik kreeg daar idd problemen mee.

Overigens is group by op kolommen die je via een left join erbij krijgt ook heel hard roepen om problemen.

Explain?

[cameodski]

MarkvE schreef op 13 mei 2004 @ 17:46:
Explain?

Je kunt dan groeperen op null values en dat is gewoonlijk toch echt niet de bedoeling. Mijn ervaring is dat een query met een group by op een kolom die NULL is door een LEFT JOIN meestal behoorlijk mank gaat.
Mede door het aantal left joins wat je gebruikt, vraag ik me af of je niet teveel op één hoop wilt vegen waardoor COUNT() bijvoorbeeld heel wat anders teruggeeft dan je wilt.

[ Voor 44% gewijzigd door cameodski op 13-05-2004 18:08 ]

[MarkvE]

Voor alsnog gaat 'ie goed, ik heb nog geen fouten kunnen vinden in het resultaat. De GROUP BY heb ik eigenlijk ook alleen gebruiker vanwege de COUNT()'s, aangezien MySQL anders een error teruggeeft. Ik heb nu trouwens ook als eerste een kolom opgegeven (bij de GROUP BY) die altijd een waarde heeft (of er moet gewoon weg geen categorieën zijn, maar dan is er dus gewoon 0 resultaat in de db aanwezig) dus in princiepe is dat probleem ook opgelost.

[ Voor 41% gewijzigd door MarkvE op 13-05-2004 20:38 ]