How to select id's which contains just special values?

Question

I need help with a sql query. I have a table like this:

ID          bookType   Date
----------- ---------- ------
1           85       01.01.2014
1           86       01.01.2014
1           88       01.01.2014
1           3005     01.01.2014
1           3028     01.01.2014
2           74       01.01.2016
2           85       01.01.2016
2           86       01.01.2016        
3           88       01.01.2015
3           3005     01.01.2015

I need a query, that returns just all id's with booktype 85, 86 and NOT id's with booktype 88,3005,3028. All the other types are not relevant, they can be included.

Example:

I just want ID 2, because there is no booktype of 88, 3005, 3028. It have the id 74, but this doesn't matter, it can be included.

I tried something like this:

SELECT bookid AS id, COUNT(bookid) AS number
FROM books
WHERE date BETWEEN '01.01.2014' and '01.01.2016'
  and booktype in (85,86)
GROUP BY bookid
HAVING COUNT(bookid) >1
MINUS
SELECT bookid AS id, count(bookid) AS number
FROM books
WHERE date BETWEEN '01.01.2014' and '01.01.2016'
  and booktype in (88,3005,3028)
GROUP BY bookid;

It doesn't work. I get every time results with booktype 88 or other included. I tried EXCEPT, but Oracle SQL Developer doesn't know it.

Answer 1

I see some inconsistency between your SQL and your columns names.

There is no bookid in the table and you miss booktype...

So assuming your first query is:

SELECT ID AS ID, COUNT(ID) AS number FROM books WHERE date
BETWEEN '2014-01-01' and '2016-01-01' and bookType in (85,86)
GROUP BY ID
HAVING COUNT(ID) >1;

This will have result set:

ID        number
 1          2
 2          2

Your second query

SELECT ID AS ID, COUNT(ID) AS number FROM books WHERE date
BETWEEN '2014-01-01' and '2016-01-01' and bookType in (88, 3005, 3028)
GROUP BY ID;

This will have result set:

ID        number
 1          3
 3          2

The MINUS operator in ORACLE returns only unique rows returned by the first query but not by the second. So the whole query will return first record set as both results of first query are different from the results of the second.

If you drop the count statement in your query you will have:

First query

SELECT ID AS ID FROM books WHERE date
BETWEEN '2014-01-01' and '2016-01-01' and bookType in (85,86)
GROUP BY ID
HAVING COUNT(ID) >1;

result set

ID 
 1          
 2

Second query:

SELECT ID AS ID FROM books WHERE date
BETWEEN '2014-01-01' and '2016-01-01' and bookType in (88, 3005, 3028)
GROUP BY ID;

result set

ID 
 1          
 3

and applying the MINUS operator you will get just 2 as required, as 1 is in the second result set.

This is just to confirm that your logic was right, but did not completely take in account the way the MINUS operates on result sets.

So your query has to be:

SELECT ID AS ID FROM books WHERE date
BETWEEN '2014-01-01' and '2016-01-01' and bookType in (85,86)
GROUP BY ID
HAVING COUNT(ID) >1
MINUS
SELECT ID AS ID FROM books WHERE date
BETWEEN '2014-01-01' and '2016-01-01' and bookType in (88, 3005, 3028)
GROUP BY ID;

Last remarks:

• I left WHERE date BETWEEN '2014-01-01' and '2016-01-01', as I thought it is relative to other requirements also if is not relevant in your example
• I left HAVING COUNT(ID) >1, as I thought it is relative to other requirements also if is not relevant in your example

Regards