[sql] filteren op meerdere records (betere uitleg inside)

[mahi]

Een vage titel, maar ik zou niet weten hoe ik dit duidelijk en kernachtig moet omschrijven

We hebben dus weer eens een query-vraag... Even een omschrijving van het probleem: We krijgen hier veel verschillende werkstukken over de vloer. Voorlopig is elk werkstuk geïndexeerd in een klassieke fichekaartenbak. Wanneer een werkstuk binnenkomt moet er snel opgezocht worden om welk stuk het precies gaat. Dat ging allemaal goed zolang er maar enkele honderden verschillende stukken waren. Maar ondertussen is het aantal al in de buurt van de 1000 stukken. Het is vrijwel onbegonnen werk om die stukken terug te vinden in die kaartenbak. Zeker niet wanneer het om stukken gaat die jaren geleden zijn gemaakt.

Daarom moet de database gedigitaliseerd worden met een flexibele zoekmogelijkheid. De database aanmaken samen met een leuk invoerformuliertje is geen probleem, maar wat de zoekmogelijkheden betreft zit ik een beetje strop. Het idee is om een systeem te maken waarbij gefilterd wordt in de database aan de hand van te selecteren kenmerken. Bijvoorbeeld (zeer simplistisch voorgesteld), door op te geven (middels checkboxes) dat het stuk rond is, van metaal gemaakt is, tussen 1 en 2 cm lang is, enz... moeten de stukken die voldoen aan AL deze kenmerken worden weergegeven zodat het zoeken slechts in een beperkte lijst gebeurt.

Omdat het over een tiental verschillende soorten kenmerken gaat (vorm, materiaal,...) met elks tot 40 verschillende kenmerken (rond, vierkant, rechthoekig,...) en er op elk moment kenmerken bij kunnen komen had ik gedacht om niet met vaste kolommen te werken. Bovendien kunnen bij heel wat stukken per soort kenmerk meerdere keuzes mogelijk zijn (bv een werkstuk dat uit staal en messing bestaat). Je zou dan al per kenmerk een kolom moeten maken en dan met 1 en 0 de "vinkjes" zetten. Het zou kunnen dat dit de juiste aanpak is, maar mij lijkt het niet het geval...

Dus momenteel heb ik een databasestructuur die sterk vereenvoudigd lijkt op het volgende:

Stukken:

code:

+--------+----------+
| StukId |   Naam   |
+--------+----------+
|      1 |   Stuk 1 |
|      2 |   Stuk 2 |
|      3 |   Stuk 3 |
+--------+----------+

Kenmerken:

code:

+-----------+---------+
| KenmerkId | Kenmerk |
+-----------+---------+
|         1 |    Geel |
|         2 |   Groen |
|         3 |    Rood |
|         4 |   Blauw |
|         5 |   Zwart |
+-----------+---------+

Eigenschappen (de connectie tussen beide bovenstaande tables waarin de kenmerken gelinkt worden met de stukken):

code:

+----+--------+-----------+
| Id | StukId | KenmerkId |
+----+--------+-----------+
|  1 |      1 |         1 |
|  2 |      1 |         3 |
|  3 |      1 |         4 |
|  4 |      2 |         1 |
|  5 |      2 |         2 |
|  6 |      2 |         3 |
|  7 |      2 |         4 |
|  8 |      3 |         2 |
+----+--------+-----------+

Nogmaals, het gaat hier om een sterk vereendvoudigd model dat louter ter illustratie dient!

En dan nu het eigenlijke probleem... Zoals eerder gezegd is het de bedoeling om te kunnen filteren op de kenmerken. Als ik in bovenstaande voorbeeld "Rood" [KenmerkId=3] aanvink in m'n form, dan moeten "Stuk 1" [StukId=1] en "Stuk 2" [StukId=2] weergegeven worden. Vink ik "Groen" [KenmerkId=2] en "Rood" [KenmerkId=3] aan, dan mag enkel nog "Stuk 2" [StukId=2] weergegeven worden.

Allemaal mooi en wel, maar hoe doe je dan nu in SQL? Als elk kenmerk z'n eigen kolom zou hebben, dan zou dit zeer eenvoudig met een SELECT StukId FROM Eigenschappen WHERE Kenmerk2 = 1 AND Kenmerk3 = 1 kunnen gebeuren, maar dat werk bij bovenstaande tabelstructuur natuurlijk niet. Als ik OR gebruik dan worden natuurlijk wel de juiste stukken weergegeven, maar ook de stukken die slechts aan één van beide kenmerken voldoen en dat is niet de bedoeling!

Iemand die me kan zeggen hoe ik dit met een query oplos? Of is gewoon de databasestructuur niet goed? Een kolom per kenmerk lijkt me thans ook geen goede oplossing, zeker niet aangezien er kenmerksoorten zijn (niet in bovenstaand voorbeeld om het geheel eenvoudig te houden) die elks weer hun eigen reeksen kenmerken hebben. En er moeten dynamisch kenmerken en kenmerksoorten kunnen aangemaakt worden. Dus qua databasevulling lijkt het systeem dat ik nu heb het meest flexibele, maar hoe kan ik daar nu in filteren zoals ik wil? Iemand raad?

[NMe]

code:

SELECT *
FROM stukken
WHERE stukid IN (
   SELECT stukid
   FROM eigenschappen
   WHERE kenmerkid IN (
      SELECT kenmerkid
      FROM kenmerken
      WHERE LCASE(kenmerk) = 'rood'
   )
)

Uiteraard kun je de laatste where ook met een IN-constructie bouwen voor meer criteria.

[Verwijderd]

Struktuur ziet er netjes uit, goed uitbreidbaar, etc.
Ervan uitgaand dat je weet dat Groen Id 2 heeft, en Rood 3, heb je 2 mogelijkheden:

Via een join:

code:

select S.StukId, S.Naam
from Stukken S
join Eigenschappen E1
on E1.StukId = S.StukId and E1.KenmerkId = 2
join Eigenschappen E2
on E2.StukId = S.StukId and E2.KenmerkId = 3

Of met subqueries (niet alle databases ondersteunen dit):

code:

select StukId, Naam
from Stukken
where exists (select Id from Eigenschappen 
              where StukId = Stukken.StukId and KenmerkId = 2)
and exists   (select Id from Eigenschappen 
              where StukId = Stukken.StukId and KenmerkId = 3)

Wanneer je niet weet dat Rood 3 is, etc., moet je de Kenmerken tabel ook nog joinen, maar het stramien blijft hetzelfde.

[mahi]

Van harte bedankt -NMe- en Afterlife! Ik heb Afterlife's manier met subqueries toegepast (omdat dat het makkelijkste te implementeren is in mijn programma) en... het werkt

Verbazingwekkend dat SQL... Telkens ik denk dat het niet meer mogelijk is, dan blijkt het toch nog simpelweg te kunnen. Ik heb mezelf ondertussen wel een boek over SQL aangeschaft, maar dat is allemaal nogal theoretisch en weinig praktijk gericht. Ik heb dat boek gisteren van voor tot achteren doorzocht en ben onder andere EXIST tegengekomen, maar het boek geeft nauwelijks of geen voorbeelden en aan de hand van de beschrijvende uitleg kom ik geen stap verder

Ik zou eens een echt goed boek moeten vinden...

[P_de_B]

mahi schreef op dinsdag 15 februari 2005 @ 08:16:


Ik zou eens een echt goed boek moeten vinden...

The Guru's Guide to Transact-SQL

Joe Celko's SQL Tips and Puzzles

De eerste is een goed werk dat in principe alle onderdelen van (Transact)SQL *) behandeld, de tweede is een goede omdat Joe Celko je op een hele ander manier naar SQL laat kijken, je zult versteld staat wat voor oplossingen er mogelijk zijn met SQL.

Zie ook: [rml][ Alg] Centrale boekentopic - part II[/rml]

*) Transact-SQL is wel de SQL Server variant van SQL, als je met een andere database werkt kun je misschien beter een boek specifiek voor die database zoeken

[ Voor 4% gewijzigd door P_de_B op 15-02-2005 08:25 ]