mysql - SQL - 返回没有特定值的行

Question

如果可能的话，想在这里寻求一些帮助吗？

我有以下疑问:-

在数据库中我们有一个名为 Linkfile 的表，该表中的“类型”均以“YG”开头。我需要返回那些不具有“YG8”类型但似乎无法做到这一点的行。我知道需要使用子查询，但我卡住了!

这是我的代码和我需要返回的字段。我只需要显示那些没有 lk.type 的“YG8”

select distinct l.description, p.displayname AS Temp,      p.compliance_status      As 'Compliant', lk.displayname, lk.type
from event e 
inner join organisation o on e.organisation_ref = o.organisation_ref
inner join opportunity opp  on e.opportunity_ref = opp.opportunity_ref
inner join event_role ev on ev.event_ref = e.event_ref
inner join address a on a.address_ref = opp.address_ref
inner join person p on ev.person_ref = p.person_ref
inner join lookup l on p.responsible_team = l.code
inner join person_type pt on  p.person_ref = pt.person_ref
inner join linkfile lk on lk.parent_object_ref = pt.person_ref
where  o.displayname LIKE '%G4S%'   and p.compliance_category = '$016' 
and lk.type like 'YG%'  and l.code_type = '2'
and a.displayname LIKE '%MOJ%' 
and pt.status = 'A'
order by l.description,  p.displayname, lk.type

Answer 1

使用以下查询:

select distinct l.description, p.displayname AS Temp,      p.compliance_status      As 'Compliant', lk.displayname, lk.type,lk.parent_object_ref
from event e 
inner join organisation o on e.organisation_ref = o.organisation_ref
inner join opportunity opp  on e.opportunity_ref = opp.opportunity_ref
inner join event_role ev on ev.event_ref = e.event_ref
inner join address a on a.address_ref = opp.address_ref
inner join person p on ev.person_ref = p.person_ref
inner join lookup l on p.responsible_team = l.code
inner join person_type pt on  p.person_ref = pt.person_ref
left join (select displayname, type,parent_object_ref from linkfile where lk.type like 'YG8%'  )lk on lk.parent_object_ref = pt.person_ref
where  o.displayname LIKE '%G4S%'   and p.compliance_category = '$016' and lk.parent_object_ref is null
 and l.code_type = '2'
and a.displayname LIKE '%MOJ%' 
and pt.status = 'A'
order by l.description,  p.displayname, lk.type;

我在类型为“YG8%”的链接文件上使用了左连接，并获取了唯一不匹配的记录