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

Issue

@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?enter image description here

Solution

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);

or

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

Answered By – Thorsten Kettner

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published