时间戳

首页 > 解决方案 > 如何检查多个表中是否存在“名称”？

mysql - 如何检查多个表中是否存在“名称”？

问题描述

如果 table1 中的所有“名称”都存在于 table2[同名的多行]和 table3 中，我愿意得到结果

已经检查了这些：
1.检查数据是否存在于多个表中
2.如何检查给定数据是否存在于多个表中（所有表都具有相同的列）？ 

select idprov, (select a.provinsi from mst_province a where a.id_tbl_mst_prov = c.idprov ) as provinsi, namasite as namasite, count(namasite) as count from form_kuesioner_pengelola c 
union
select idprov, (select a.provinsi from mst_province a where a.id_tbl_mst_prov = c.idprov ) as provinsi, namasite as namasite, count(namasite) as count from form_kuesioner_manfaat c group by namasite 
union
select idprov, (select a.provinsi from mst_province a where a.id_tbl_mst_prov = c.idprov ) as provinsi, namalokasi as namasite, count(namalokasi) as count from form_kuesioner_surveyor c

因此，上面代码的结果让我得到了 table1 中存在的所有“名称”，包括其他表中不存在的“名称”。

我希望我的结果像 

 table
    'name1' = 1 (exist) from form_kuesioner_pengelola  
    'name1' = 0 (not exist) from form_kuesioner_manfaat  
    'name1' = 0 (not exist) from form_kuesioner_surveyor

我知道我离“近”不远，你能启发我吗？

谢谢

标签： mysqlsql

解决方案

用于NOT EXISTS获取一个表中存在的名称，而不是其他表中的名称：

select *
from form_kuesioner_pengelola p
where name = 'xyz'
  and not exists (select * from form_kuesioner_manfaat m where m.name = p.name)
  and not exists (select * from form_kuesioner_surveyor s where s.name = p.name)
order by name;

（如果在andname中从不为空，您甚至可以用更简单的子句替换这些子句。）form_kuesioner_manfaatform_kuesioner_surveyorNOT EXISTSNOT IN

更新：这是显示名称存在于哪些表中的方法。

select
  exists (select * from form_kuesioner_pengelola where name = 'xyz') as in_pengelola,
  exists (select * from form_kuesioner_manfaat where name = 'xyz') as in_manfaat,
  exists (select * from form_kuesioner_surveyor where name = 'xyz') as in_surveyor;

更新：这是一个检查表中所有名称是否存在的查询。这有点笨拙，因为 MySQL 仍然不支持完全外连接。

select
  names.name,
  count(p.name) as in pengelola,
  count(m.name) as in in_manfaat,
  count(s.name) as in in_surveyor
from
(
    select name from form_kuesioner_pengelola
    union
    select name from form_kuesioner_manfaat
    union
    select name from form_kuesioner_surveyor
) names
left join form_kuesioner_pengelola p on p.name = names.name
left join form_kuesioner_manfaat m on m.name = names.name
left join form_kuesioner_surveyor s on s.name = names.name
group by names.name
group by names.name;

推荐阅读