'SQL statement in JPA to get all equipments that arent rented at this day and arent created by the user

I want to get all Equipments, that arent rented (wirtten down in the contract-table) and arent created by the user who is searching for equipments.

this is what i have:

public List<Equipment> findAllFreeEquipmentsByUserIdAndRentingDate(Integer userId, LocalDate rentingDate){
    String sql =
            "SELECT EQUIPMENT.* FROM EQUIPMENT " +
                    "WHERE EQUIPMENT.ID != (SELECT CONTRACT.EQUIPMENT_ID FROM CONTRACT WHERE RENTING_DATE=?) " +
                    "AND EQUIPMENT.OWNER_ID !=? ";
    List<Equipment> equipments = em.createNativeQuery(sql, Equipment.class)
            .setParameter(1, rentingDate)
            .setParameter(2, userId)
            .getResultList();
    return equipments;
}

When iam testing this with an Equipment that doesnt have a contract already, the result is always 0, bcs the (SELECT CONTRACT.EQUIPMENT_ID FROM CONTRACT WHERE RENTING_DATE=?) wont find any id.

I dont know how to fix this problem..



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source