How to select all items that have no orders?(that are not on any receipt)-CodePudding

@Query(value="SELECT i.name\n"  
            "FROM item as i\n"  
            "INNER JOIN items_receipts as ir\n"  
            "    ON i.id = ir.item_id\n"  
            "INNER JOIN receipt as r\n"  
            "    ON ir.receipt_id = r.id\n"  
            "GROUP BY i.id\n"  
                "HAVING COUNT(r.id)=0"
            ,nativeQuery = true)
    List<String> fiveSales();

Why this query doesn't seem to work? Is it because the count is null or something else?

CodePudding user response：

You are joining the tables. Thus you get all the ordered items. Now you look at that data set and for every item in that dataset you count the rows. The problem is: you want the items that are not in the data set.

For lookups use [NOT] EXISTS or [NOT] IN.

select *
from item
where id not in (select item_id from items_receipts);

select *
from item i
where not exists (select null from items_receipts ir where ir.item_id = i.id);