SQL question: excluding records

Question

I have a database (NexusDB (supposedly SQL-92 compliant)) which contains and Item table, a Category table, and a many-to-many ItemCategory table, which is just a pair of keys. As you might expect, Items are assigned to multiple categories.

I am wanting to all the end user to select all items which are

ItemID | CategoryID
--------------------------------
01 | 01
01 | 02
01 | 12

02 | 01
02 | 02
02 | 47

03 | 01
03 | 02
03 | 14
etc...

I want to be able to select all ItemID's that are assigned to Categories X, Y, and Z but NOT assigned to Categories P and Q.

For the example data above, for instance, say I'd like to grab all Items assigned to Categories 01 or 02 but NOT 12 (yielding Items 02 and 03). Something along the lines of:

SELECT ItemID WHERE (CategoryID IN (01, 02))

...and remove from that set SELECT ItemID WHERE NOT (CategoryID = 12)

This is probably a pretty basic SQL question, but it's stumping me at the moment. Any help w/b appreciated.

Answer 1

You could try with EXCEPT

SELECT ItemID FROM Table
EXCEPT
SELECT ItemID FROM Table
WHERE
CategoryID <> 12

Answer 2

SELECT i.ItemID, ic.CategoryID FROM Item AS i
INNER JOIN ItemCategory ic
ON i.ItemID = ic.ItemID
WHERE ic.CategoryId = 1 OR ic.CategoryId = 2

Of course you need to put in the WHERE clause what categories you want to get.

Answer 3

For the simple case that you have with a low and known number of categories you can simply use several joins to check for existence and non-existence:

SELECT
     ItemID
FROM
     Items I
INNER JOIN ItemCategories IC1 ON IC1.ItemID = I.ItemID AND IC1.CategoryID = '01'
INNER JOIN ItemCategories IC2 ON IC2.ItemID = I.ItemID AND IC2.CategoryID = '02'
LEFT OUTER JOIN ItemCategories IC3 ON IC3.ItemID = I.ItemID AND IC3.CategoryID = '12'
WHERE IC3.ItemID IS NULL

For a more general case, given an unknown number of items in the match and don't match lists, you can use the following query. I've used a table variable (available in SQL Server) for each of the lists, but you can use a select against an actual table or a list of variables/parameters as needed. The idea remains the same:

SELECT
     ItemID
FROM
     Items I
WHERE
     (
      SELECT COUNT(*)
      FROM ItemCategories IC1
      WHERE IC1.ItemID = I.ItemID
        AND IC.CategoryID IN
           (SELECT CategoryID FROM @MustHaves)
      ) = (SELECT COUNT(*) FROM @MustHaves) AND
      (
      SELECT COUNT(*)
      FROM ItemCategories IC1
      WHERE IC1.ItemID = I.ItemID
        AND IC.CategoryID IN
           (SELECT COUNT(*) FROM @MustNotHaves)
      ) = 0

Answer 4

I want to be able to select all ItemID's that are assigned to Categories X, Y, and Z but NOT assigned to Categories P and Q.

I can't confirm from the NexusDB documentation on SELECT that they support subqueries, but they do support LEFT OUTER JOIN and GROUP BY. So here's a query that works within these restrictions:

SELECT i1.ItemID
FROM ItemCategory i1
  LEFT OUTER JOIN ItemCategory i2
    ON (i1.ItemID = i2.ItemID AND i2.CategoryID IN ('P', 'Q'))
WHERE i1.CategoryID IN ('X', 'Y', 'Z')
  AND i2.ItemID IS NULL
GROUP BY i1.ItemID
HAVING COUNT(i1.CategoryID) = 3;