monterinio monterinio - 1 month ago 7
SQL Question

Subquery with inner join and not exist(?)

I have this code:

SELECT b.id_book
INTO var_idbook
FROM ORDERS o
INNER JOIN SIGNATURES s
ON o.signature=s.signature
INNER JOIN BOOKS b ON s.id_book=b.id_book
WHERE ((b.genre=var_genre))
GROUP BY b.id_book
ORDER BY COUNT(o.ID_ORDER) DESC
FETCH FIRST ROW ONLY;


This code works fine and it's goal is to give a id_book of a book that is the most popular in certain genre. I'd like to check if a user borrowed this book before because I don't want to recommend him the book that he read once.
I have a table named ORDERS where I have history of orders and attrubutes: ID_ORDER, ID_READER and SIGNATURE and also table SIGNATURES where I have SIGNATURE and ID_BOOK. When I invoke this method I use ID_READER but I have no idea how to modify this code to have a history checked.
Thank you for your help in advance.

Answer

You can use the not exists in the WHERE clause:

SELECT b.id_book 
  INTO var_idbook
  FROM ORDERS o
 INNER JOIN SIGNATURES s
    ON o.signature=s.signature
 INNER JOIN BOOKS b 
    ON s.id_book=b.id_book
 WHERE b.genre=var_genre
   AND NOT EXISTS (SELECT 1 
                     FROM ORDERS o2 
                    INNER JOIN SIGNATURES s2
                       ON o2.signature = s2.signature
                    WHERE o2.id_reader = var_reader
                      AND s2.id_book   = s.id_book)
GROUP BY b.id_book
ORDER BY COUNT(o.ID_ORDER) DESC
FETCH FIRST ROW ONLY;

Since I don't have a proper dataset I can't test it, but the idea is to have a lookup for the id_book to see if the user ordered it and filter it out. The subquery is joined to the outer query by id_book and a parameter var_reader is used to select the user.

EDIT: For an interesting discussion on EXISTS / NOT EXISTS and IN / NOT IN please refer to the link below:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684