Left join with <> condition (not equal to) returns wrong rows

Question

HQL Statement:

String hql = "SELECT DISTINCT track FROM TrackEntity track " +
                "LEFT JOIN track.releaseRelations releaseRelation " +
                "LEFT JOIN releaseRelation.release.publisherProducerArtists releaseArtist " +
                "LEFT JOIN releaseArtist.person releaseArtistPerson " +
                "LEFT JOIN releaseRelation.release.publisherProducerGroupCasts releaseGroupCast " +
                "LEFT JOIN releaseGroupCast.artistRelations releaseGroupCastArtistRelations " +
                "LEFT JOIN releaseGroupCastArtistRelations.artist.person releaseGroupCastPerson " +
                "WHERE (releaseArtistPerson.id <> " + personId +
                " OR releaseArtistPerson.id IS null) " +
                "AND (releaseGroupCastPerson.id <> " + personId +
                " OR releaseGroupCastPerson.id IS null ) "

The goal is to find all tracks where the person is not related to. So the person should not be in the publisherProducerArtists and publisherProducerGroupCasts.

The WHERE-statement filters out just the one row where the personId is equal. But if one row is equal, all rows of this track should be filtered out. Probably I need something like NOT IN? But then I have to use SUBSELECTs right?

What is the common way to solve this?

Thanks for help :)

score 0 · Answer 1 · answered Jun 06 '22 at 12:28

Need to understand the below statement:

(releaseArtistPerson.id <> " + personId + " OR releaseArtistPerson.id IS null)

The first expression (of composite expression joined with OR operator) will return true when Persion's Id is not equals to given Number/String as PersionID which also means if Persion's Id is Null then also that record is selected.

Therefore, first need to remove OR section where checking with IS Null.

If still issue persists, try Not In operator which works with HQL like:

HQL:
from Person p where p.name not in (:nameList)

where,
List<String> nameList = new ArrayList<>();
nameList.add("abc");
nameList.add("def");

Left join with <> condition (not equal to) returns wrong rows

1 Answers1