I am trying to construct a query to handle multiple filters in ISQL. I have a query that provides the answers I need when I'm using MSSQL, but it uses the INTERSECT command, which appears not to be present in ISQL.
I have the following data:
| ItemID | Label | ItemGroupID_1 | ItemGroupID_2 | ItemGroupID_3 |
|---|---|---|---|---|
| 1 | Champagne Jean Pernet Cuvee | WI | Sparkling | White |
| 2 | Champagne Jean Pernet Millesime 2008 | WI | Sparkling | White |
| 3 | Champagne Jean Pernet Millesime 2009 | WI | Sparkling | White |
| 4 | Chateau de Montmirail Gigondas Cuvee 2017 | WI | Still | Blue |
| 5 | Chateau de Montmirail Gigondas Cuvee 2018 | WI | Still | Red |
| 6 | Chateau de Montmirail Cotes du Rhone 2015 | WI | Still | Red |
| 7 | Toutigeac Bordeaux Blanc 2018 | WI | Still | Black |
| 8 | Toutigeac Bordeaux Blanc 2019 | WI | Still | White |
| 9 | Domaine Feuillat-Juillot Montagny 2017 | WI | Null | Black |
| 10 | Domaine Alphonse Dolly Pouilly Fume 2017 | WI | Still | White |
I want users to be able to select from multiple filters to find, for example, all of the items where ItemGroupID_1 = 'WI', ItemGroupID_2 = 'Still', and ItemGroupID_3 = 'Red'. I have been told by the programmers that I cannot do a simple query like
SELECT ItemID from Items where ItemGroupID_1 = 'WI' and ItemGroupID_2 = 'Still' and ItemGroupID_3 = 'Red'
In MSSQL, this works:
SELECT ItemID AS A FROM Items WHERE ItemGroupID_1 = 'WI'
INTERSECT
SELECT ItemID AS A FROM Items WHERE ItemGroupID_2 = 'Still'
INTERSECT
SELECT ItemID AS A FROM Items WHERE ItemGroupID_3 = 'Red'
The problem is that the query needs to run in ISQL. And it doesn't look like ISQL has the INTERSECT command. (See Sybase's woefully inadequate technical reference.)
However, UNION does work in ISQL. Fortunately, A AND B is the same thing as NOT-A NOR NOT-B.
In MSSQL this works and returns the results I want:
SELECT ItemID, Label, ItemGroupID_1, ItemGroupID_2, ItemGroupID_3 AS A FROM Items WHERE ItemID NOT IN
(SELECT ItemID AS A FROM Items WHERE ItemGroupID_1 <> 'WI' or ItemGroupID_1 is null
UNION
SELECT ItemID AS A FROM Items WHERE ItemGroupID_2 <> 'Still' or ItemGroupID_2 is null
UNION
SELECT ItemID AS A FROM Items WHERE ItemGroupID_3 <> 'Red' or ItemGroupID_3 is null)
The subquery works in ISQL and returns everything I don't want. But when I add in the statement SELECT ItemID, Label, ItemGroupID_1, ItemGroupID_2, ItemGroupID_3 AS A FROM Items WHERE ItemID NOT IN I get:
Could not execute statement.[UltraLite Database] Syntax error near 'UNION' [SQL Offset 132]
SQLCODE=-131, ODBC 3 State='42000'
Line 3
I came across the ISQL command EXISTS and NOT EXISTS, but NOT EXISTS returns no values.