Onmogelijke JOIN?

[Yannick]

Ik heb een database gemaakt voor het volgende scenario:
Ik heb een Producten tabel en een Criteria tabel. De producten kunnen wel of niet aan een van de 40 criteria voldoen. Om dit vast te leggen heb ik een extra tabel gemaakt (Producten_Criteria) die twee kolommen heeft: ProductID en CriteriumID. Wanneer een product aan zo'n criterium voldoet wordt er een record toegevoegd aan deze koppeltabel. (is dit sowieso een goeie opzet?).

Nu wil ik een query maken met alle producten die aan meerdere criteria voldoen. Wat ik tot nu toe heb is dit:

code:

SELECT Products.Name, Products_Criteria.CriteriumID
FROM Products INNER JOIN Products_Criteria ON Products.ID =
     Products_Criteria.ProductID
WHERE Products_Criteria.CriteriumID=1 AND
    Products_Criteria.CriteriumID=2

Logischerwijs kan aan de WHERE clause nooit worden voldoen op deze manier, dus geeft hij niks terug.
Iemand een oplossing voor deze puzzel?

Thanks,
Yannick Smits

[Tsjipmanz]

een OR ipv een AND

[Yannick]

Nee, ik wil alleen de producten die aan beide criteria voldoen. Dus niet een van de twee (bij OR).

[Marcj]

lijkt me niet dat één variabele twee waardes kan hebben, dus moet het wel OR zijn (verder weet ik niets van MySQL af)

[Verwijderd]

Een veld kan nooit 2 waarden bezitten, en dat is precies wat je hier vraagt. Dit werkt wel:

SELECT Products.Name, Products_Criteria.CriteriumID
FROM Products INNER JOIN Products_Criteria ON Products.ID =
Products_Criteria.ProductID
WHERE Products_Criteria.CriteriumID IN (1,2)

Dit werkt zo in access, je moet maar even kijken wat de syntax voor de haakjes precies moet zijn in jouw programma.

[justmental]

Je moet product_criteria meerdere malen joinen en aliassen gebruiken.
Eenmaal voor elk criterium.

edit:
syntactisch beter gemaakt

[justmental]

Op donderdag 15 november 2001 16:35 schreef Sjoerd het volgende:
Een veld kan nooit 2 waarden bezitten, en dat is precies wat je hier vraagt. Dit werkt wel:

SELECT Products.Name, Products_Criteria.CriteriumID
FROM Products INNER JOIN Products_Criteria ON Products.ID =
Products_Criteria.ProductID
WHERE Products_Criteria.CriteriumID IN (1,2)

Dit werkt zo in access, je moet maar even kijken wat de syntax voor de haakjes precies moet zijn in jouw programma.

Dan heb 1 OR 2.
AND was de bedoeling.

[Yannick]

Even voor de duidelijkheid: Als een product aan een criterium voldoet wordt er een record toegevoegd aan de koppel tabel (Products_Criteria). Die komt er dan voor Product met productID 9 bijvoorbeeld als volgt uit te zien:

code:

ProductID CriteriumID
9     1
9     2
9     3
9     5
9     8
9     12
9     15
9     16
9     17
9     21
9     24

[Verwijderd]

OK ik snap het nu, idd geen OR, maar je wilt een id kan meerdere keren voorkomen en waarde 1 of 2 bezitten. check:

SELECT Veld1
FROM Tabel1 where
veld1 in (select veld1 from tabel1 where veld2=1)
and
veld1 in (select veld1 from tabel1 where veld2=2)
group by veld1

de tabel1 ziet er zo uit:
Veld1Veld2
a2
b3
a1
d3
e1
f2

Dus nu zou alleen a eruit moeten komen, omdat de de waarde 1 en 2 bij zich heeft. De bovenstaande sql doet dat. Ik hoop dat je ;m snapt, had niet veel zin om jouw sql volledig te gaan nadoen

[Yannick]

Op donderdag 15 november 2001 16:35 schreef justmental het volgende:
Je moet product_criteria meerdere malen joinen en aliassen gebruiken.
Eenmaal voor elk criterium.

edit:
syntactisch beter gemaakt

Wordt dat niet een beetje veel overhead voor de DB als je 20 criteria wilt checken?

[justmental]

Op donderdag 15 november 2001 16:43 schreef Yannick het volgende:

[..]

Wordt dat niet een beetje veel overhead voor de DB als je 20 criteria wilt checken?

Doe je weinig aan.
Als het vaste criteria zijn dan kun je je datamodel wijzigen anders goed indexeren enzo.

[chem]

Op donderdag 15 november 2001 16:43 schreef Yannick het volgende:

[..]

Wordt dat niet een beetje veel overhead voor de DB als je 20 criteria wilt checken?

ik vind 40 joins knap veel

[Yannick]

Het werkt! Zat al met m'n handen in het haar, heb dat Order by gedoe nooit gesnapt (en nog steeds niet echt), toch maar eens in verdiepen dan.
Bedankt allemaal!

[rstegers]

Volgens mij is het inderdaad niet heel efficient. Ligt er een beetje aan of de DB zelf optimalisatie doet. Het is ook mogelijk een tijdelijke tabel te maken met daarin alle criteria van het betreffende product en daaruit dan verder te selecteren. Als je dit dan in een transactie stopt mag het geen problemen geven.

Helaas weet ik niet precies de syntax uit m'n hoofd maar wat je zo'n beetje doet is het volgende (in pseudo sql, wie helpt?)

create table Temp <Int criteria id>;

insert criteriaID where productID = _PRODUCT_ from Criteria into Temp;

select productID from products where (1 in Temp) and (2 in Temp);

drop table Temp;

Dit is zo'n beetje het idee. Ik hoop dat je er iets mee kan als het voorgaande te traag werkt bij grote selecties.

Gr Ruud

[Yannick]

Ik had vooral m'n twijfels bij die joins maar met de oplossing van Sjoerd denk ik dat performance wel goed zit.

[Yannick]

Hmm performance toch wel beetje het probleem bij het checken van 30 criteria.
Terwijl de DB nog redelijk klein is:
Producten: 18 records
Producten_Criteria: 270 records

Doet MS SQL Server er toch nog 5 seconde over op een compu met onderstaande specs!
Of zou dat komen omdat ik de query combineer met een aantal andere JOINs, die opzichzelf in eerste instantie niet zoveel tijd namen?

code:

SELECT Producten.Naam AS Productnaam, Productcategorieen.Naam AS Categorie, ProducentenLeveranciers_1.Bedrijfsnaam AS Producent, ProducentenLeveranciers.Bedrijfsnaam AS Leverancier, Producten.DatumWijziging AS Wijzigingsdatum, Producten.ID AS ID, Producten.LeverancierID, Producten.ProducentID, Producten.ProductcategorieID
FROM ProducentenLeveranciers AS ProducentenLeveranciers_1 
INNER JOIN (ProducentenLeveranciers INNER JOIN (Productcategorieen INNER JOIN Producten ON Productcategorieen.ID = Producten.ProductcategorieID) ON ProducentenLeveranciers.ID = Producten.LeverancierID) ON ProducentenLeveranciers_1.ID = Producten.ProducentID
WHERE
Producten.Status = 'T'
AND
Producten.ID in (select ProductID from Producten_Criteria where CriteriumID=1) AND
--plus nog 30x bovenstaande regel met ander CriteriumID 
Producten.ID in (select ProductID from Producten_Criteria where CriteriumID=32)
GROUP BY Producten.ID, Producten.Naam, Productcategorieen.Naam,ProducentenLeveranciers_1.Bedrijfsnaam, ProducentenLeveranciers.Bedrijfsnaam, Producten.DatumWijziging, Producten.LeverancierID, Producten.ProducentID, Producten.ProductcategorieID
ORDER BY Producten.Naam

[Delphi32]

code:

SELECT product.productID, COUNT(product_criteria.criteriumID)
FROM product 
  INNER JOIN product_criteria ON product.productID =     product_criteria.productID
GROUP BY product.productID
HAVING COUNT(product_criteria.criteriumID) > 1

levert me volgens mij een tabel op met alle producten die aan meer dan 1 criterium voldoen.

De HAVING voorwaarde wordt niet zo vaak gebruikt, want het schijnt nogal een lastige te zijn (wordt ook niet altijd ondersteund), maar de essentie is dat je in een HAVING alleen voorwaarden op de geaggregeerde kolom mag opnemen (in dit geval dus de COUNT)

[Woy]

Op vrijdag 16 november 2001 00:36 schreef Delphi32 het volgende:

code:

SELECT product.productID, COUNT(product_criteria.criteriumID)
FROM product 
  INNER JOIN product_criteria ON product.productID =     product_criteria.productID
GROUP BY product.productID
HAVING COUNT(product_criteria.criteriumID) > 1

levert me volgens mij een tabel op met alle producten die aan meer dan 1 criterium voldoen.

Ja je krijgt wel de producten die aan meer als 1 criterium voldoen maar je kan niet zelf aangeven aan welke criteria het moet voldoen. je kan op deze manier alleen berekenen aan hoeveel criteria een product voldoet

[paulh]

Probeer een having altijd te voorkomen want die maakt meestal niet gebruik van indexen want een having werkt alleen op een al gegenereerde resultset.

[dusty]

Op donderdag 15 november 2001 16:54 schreef chem het volgende:
ik vind 40 joins knap veel

Is het ook, echter ga je dat natuurlijk niet handmatig doen, maar ga je dat dynamisch laten genereren, eventueel in een package/procedure vorm waardoor het erg snel uitgevoerd kan worden.

Kind kan de was doen