SQL-query met, in feite, een conditioneel element

[Christiaan]

Ik ben een site aan het maken waarin een wat rottige query voorkomt waar ik niet uitkom. Ook al schrijf ik al vele jaren complexe queries, deze heeft me al heel wat hoofdpijn veroorzaakt - terwijl ik denk dat de oplossing niet eens zo moeilijk moet zijn.

De situatie is deze:
Er is een tabel met daarin records, en deze records kunnen toegekend worden aan 0 of meer categorieen. In een andere tabel wordt, per record uit de originele tabel, aangegeven welke categorieen daar bij horen. Per record uit de originele tabel kunnen daar dus 0 of meer dan 0 verwijzingen voorkomen. Om wat filteren in wat gebruikers mogen zien, wordt aangegeven welke gebruikers welke categorieen mogen zien. Een gebruiker mag een record dan zien indien:

1. Een record geen geassocieerde categorieen kent (en dus on-categoriseerd is)
2. Een record een geassoccieerde categorie heeft waar men bij mag (1 matchende categorie is genoeg om het record te mogen zien)

Ik moet nu dus een query hebben die dit doet:

1. Haal alle records op uit de originele tabel, laten we zeggen 'tijdschriften'
2. Haal de bijbehorende categorieen op uit, laten we zeggen, 'categorieen'
3. ALS er 0 categorieen terugkomen, dan hoeft er verder niet gefilterd te worden
4. MAAR als er >0 categorieen terugkomen, dan moet gekeken worden of de gebruiker die 1 of meer van die categorieen mag zien

De query is *nu* ongeveer zo:

SELECT tijdschriften.tijdschriftID, tijdschrift.titel
FROM tijdschriften, categorieen
WHERE tijdschriften.categoryID = categorieen.categorieID
AND tijdschrift.categorieID IN ([lijst met IDs van categorieen die men mag lezen)

Dit werkt natuurlijk niet, want als er geen geassocieerde categorieen zijn werkt de join niet, en komt er niks terug. Met een left join lukt het me ook niet, want dan nog moet de controle of de categorieID voorkomt in de 'green list' alleen uitgevoerd worden als er uberhaupt geassocieerde categorieen zijn. Ik heb 1 query die het goed doet, maar deze is weer onhandig groot:

SELECT tijdschriften.tijdschriftID, tijdschrift.titel
FROM tijdschriften
WHERE
((SELECT COUNT(categorieen.categoryID) FROM categorieen WHERE categorieen.categoryID = tijdschriften.tijdschriftID) = 0)
OR
(SELECT COUNT(categorieen.categoryID) FROM categorieen WHERE categorieen.categoryID = tijdschriften.tijdschriftID AND categorieen.categoryID IN ([green list])) > 0)

Ik kan me echter niet voorstellen dat er geen makkelijkere en snellere manier is. Het is overigens niet echt een optie om de tabelstructuur te veranderen. Ik heb al geprobeerd om tijdschriften die ongecategoriseerd is in te delen in een categorie die 'ongecategoriseerd' heet, maar dat levert in de rest van het systeem allerlei vervelende complicaties op die nog veel lastiger zijn.

Wie kan me helpen bij dit probleem?

[ACM]

code:

SELECT tijdschriften.tijdschriftID, tijdschrift.titel
FROM
  tijdschriften
  NATURAL LEFT JOIN categorieen
WHERE
categorieen.categorieID IS NULL
OR
categorieen.categorieID IN ([lijst met IDs van categorieen die men mag lezen)

Lijkt me wel aardig voor je?

[Verwijderd]

ik zou eens expirimenteren met een left outer join op categorien. als je categoryID opvraagd is deze NULL als er geen categorien zijn, anders kan je dus matchen op green list

edit:
zie dus ook ACM's post, alleen in SQL Server is Natural Join denk een outer join

[ Voor 22% gewijzigd door Verwijderd op 17-02-2005 11:17 . Reden: ACM ]

[ACM]

NATURAL is een keyword dat verteld dat de database zelf maar moet uitzoeken op welke velden er gejoined moet worden. Dat is minder werk dan de veldnamen intikken en doet verder hetzelfde, zeker als voorbeeld is dat dan handiger
LEFT JOIN is een afkorting voor LEFT OUTER JOIN, die OUTER hoeft er niet bij omdat een LEFT JOIN per definitie OUTER is, zelfde geldt trouwens voor een RIGHT en een FULL join.

[ Voor 8% gewijzigd door ACM op 17-02-2005 11:18 ]

[Verwijderd]

ACM schreef op donderdag 17 februari 2005 @ 11:18:
NATURAL is een keyword dat verteld dat de database zelf maar moet uitzoeken op welke velden er gejoined moet worden. Dat is minder werk dan de veldnamen intikken en doet verder hetzelfde, zeker als voorbeeld is dat dan handiger
LEFT JOIN is een afkorting voor LEFT OUTER JOIN, die OUTER hoeft er niet bij omdat een LEFT JOIN per definitie OUTER is, zelfde geldt trouwens voor een RIGHT en een FULL join.

en zo leer je elke dag weer iets nieuws.....

toch vind ik het persoonlijk beter om outer er wel bij te definieren, om onduidelijkheden weg te nemen wat de default outer/inner nu is

[Christiaan]

ACM schreef op donderdag 17 februari 2005 @ 11:12:

code:

SELECT tijdschriften.tijdschriftID, tijdschrift.titel
FROM
  tijdschriften
  NATURAL LEFT JOIN categorieen
WHERE
categorieen.categorieID IS NULL
OR
categorieen.categorieID IN ([lijst met IDs van categorieen die men mag lezen)

Lijkt me wel aardig voor je?

Wel verdorie! Waarom lukte mij dat nou niet? Maar dit lijkt te werken, inderdaad. Ik voel me nu alleen heel dom, want dit had ik ook wel kunnen verzinnen. Zit er alleen toch al twee dagen mee te rotzooien. Misschien komt het ook wel omdat de eigenlijke tabellen wel aanzienlijk complexer zijn dan in bovenstaand voorbeeld. Het principe werkt echter wel, geloof ik. Ik ga het straks even testen. Eerst nog even wat studie-werk. In elk geval heel erg bedankt voor jullie tijd!

p.s: de natural join werkt dus niet zo goed, want de join vindt eigenlijk plaats op drie kolommen ipv de 1e uit de tabel. In de categorie-tabel worden namelijk van alle objecten in het systeem categorieen bijgehouden, die ook nog eens per site kunnen verschillen. De echte tabel is dus: categorieID, objectID, recordID, siteID. Dan is een gewone, gedefinieerde, left join beter.

[ Voor 19% gewijzigd door Christiaan op 17-02-2005 11:38 ]