具有<>条件(不等于)的左连接返回错误的行

Question

HQL语句:

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

目标是找到与这个人无关的所有轨迹。所以这个人不应该出现在publisherProducerArtists和publisherproducergroupcast中。

where语句只过滤出personId相等的一行。但如果一行相等，这条轨道的所有行都应该被过滤掉。也许我需要像NOT IN这样的东西?然后我必须使用SUBSELECTs，对吧?

解决这个问题的常见方法是什么?

谢谢你的帮助:)

Answer 1

需要理解下面的语句:

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

当Persion的Id不等于给定的Number/String作为PersionID时，第一个表达式(与OR操作符连接的复合表达式)将返回true，这也意味着如果Persion的Id为Null，那么也选择了该记录。

因此，首先需要删除OR部分，其中检查为Null。

如果问题仍然存在，请尝试Not In操作符，该操作符适用于HQL，如:

HQL:
from Person p where p.name not in (:nameList)
where,
List<String> nameList = new ArrayList<>();
nameList.add("abc");
nameList.add("def");