in einer LibreOffice Firebird Datenbank habe ich diese Tabelle:
Code: Alles auswählen
SELECT "itemID", "topicID" FROM "relItemTopic"
itemID topicID topicName
1 1 one
1 2 two
1 3 three
1 4 four
1 5 five
2 1 one
3 1 one
4 2 two
5 4 four
6 1 one
6 2 two
6 3 three
6 4 four
6 5 five
6 6 six
Mit der Abfrage
Code: Alles auswählen
SELECT "relItemTopic"."itemID"
FROM "relItemTopic"
INNER JOIN "topic" ON "relItemTopic"."topicID" = "topic"."ID"
WHERE "topic"."name" IN ('one', 'five')
GROUP BY "relItemTopic"."itemID"
HAVING COUNT(DISTINCT "topic"."name") = 2
Für noch mehr "topic"."name":
Code: Alles auswählen
SELECT "relItemTopic"."itemID"
FROM "relItemTopic"
INNER JOIN "topic" ON "relItemTopic"."topicID" = "topic"."ID"
WHERE "topic"."name" IN ('one', 'two', 'three', 'four', 'six')
GROUP BY "relItemTopic"."itemID"
HAVING COUNT(DISTINCT "topic"."name") = 5
Diese Abfrage funktioniert nicht:
Code: Alles auswählen
SELECT "relItemTopic"."itemID", "topic"."name"
FROM "relItemTopic"
INNER JOIN "topic" ON "relItemTopic"."topicID" = "topic"."ID"
WHERE
( LOWER ( "topic"."name" ) LIKE '%' || LOWER ( :name1 ) || '%' OR CAST( :name1 AS VARCHAR ( 150 ) ) IS NULL )
AND
( LOWER ( "topic"."name" ) LIKE '%' || LOWER ( :name2 ) || '%' OR CAST( :name2 AS VARCHAR ( 150 ) ) IS NULL )
nur "name1": korrekte Datensätze
verschiedene eingaben in"name1" und "name2": keine Datensätze
Grüße
Malt
Version: 7.5.2.2 (X86_64) /
OS: Windows 10.0 Build 22621