php - SQL 查询如果两个条目相等，则从三个表中选择四个条目

Question

我有三个不同的表:

1. Table Business
ID, Name, fk_Input, fk_Output
    
2. Table Input
Id, FK_Business_id, Name

3.
Id, FK_Business_id, Name

我在SQLFiddle上创建了一个数据库示例.

现在只想检查哪个企业具有相同的output.name，就像其他企业的Input.name一样，并显示结果，同时显示企业名称和可以共享的资源。

举一个更好理解的例子

1. Table Business
ID, Name, fk_Input, fk_Output

1, AdventureA, 1,1
2, BuinsnessB, 2,2
3, CompanyC,   3,3

2.Table Input
Id, FK_Business_id, Name
1, 1(AdventureA),coal
2, 1(AdventureA),Air
3, 2(BusinessB) ,Cooper
4, 2(BusinessB) ,Power
5, 3(CompanyC)  ,Wood

3.Table Output
Id, FK_Business_id, Name
1, 1(AdventureA), Power
2, 2(BusinessB) , Cooperbar     
3, 3(CompanyC)  , Power

结果应该是:

AdventureA has Power for BusinessB
CompanyC   has Power for BusinessB

或者换句话说，像这样

BusinessnameOutput, BusinessnameInput, NameInput, NameOutput

我已经尝试过

SELECT DISTINCT Business.Name, Output.Name, Input.Name FROM Business, Input, Output 
WHERE Output.Name = Input.Name

但是会导致很多行毫无意义

也尝试过

SELECT Business.Name, Input.Name, Output.Name 
FROM Unternehmen 
INNER JOIN Input ON Input.FK_Business_id,   = Business.ID 
INNER JOIN Output ON Output.FK_Business_id = Business.ID
WHERE Input.Name = Output.Name

但结果没有条目。

几乎正确结果的查询是

SELECT Business.Name, Input.Name, Output.Name FROM Business,Input, Output WHERE Input.Name = Output.Name GROUP BY Input.RessourceName

但它只显示一个企业名称，而不是两个具有相同输出和输入的企业名称。

不知道如何解决这个困境？任何人有一个想法，那就太好了。

Answer 1

您可以使用内部联接和适当的联接条件

select distinct concat( Business.Name, ' has ',  Output.Name, ' for ', Business2.name) 
from Business 
inner join Output on Business.fk_Output = Output.id
inner join Input on Input.name = Output.name
inner join Business as Business2 on Input.fk_business_id = Business2.id ;

http://sqlfiddle.com/#!9/de4efa/6