一个表中的字段值是另一个表中的列名的连接表 - Join table where field value in one table is a column name in another table

Join table where field value in one table is a column name in another table

本文关键字：另一个连接字段一个 | 更新日期: 2023-09-27

我有两个表service_category和category_mapping。我想显示特定类别中列出的类别和公司数量

Table service_category:
id    cattegory          mappingid
1     Construction          cc
2     InteriorDesign        idn
3     TechnicalWork         tw
4     MarineConstruction    mc
Table category_mapping:
id    companyid        cc    idn    tw   mc
1         4            Y      Y     N    N
2         5            N      Y     N    Y
3         6            N      N     N    Y
4         7            Y      N     N    N

像这样的

SELECT  sc.*,(select count(*) from category_mapping where `XYZ`='Y' ) as companycount  FROM  service_category as sc

有人能帮我吗?

这将有助于只提及类别..如果没有更多的类别..

select id, cattegory ,mappingid ,(select  count(*) from category_mapping  where cc='Y') as count
from 
service_category
where mappingid='CC'
union 
select id, cattegory ,mappingid ,(select  count(*) from category_mapping  where idn='Y') as count
from 
service_category
where mappingid='idn'