SQL多连接多对多

Question

我有5张表：

表报告

Report_Id | Report_name
-----------------
      1     | Income
      2     | Outcomes
      3     | Costs

多对多表ReportsIpRel

Ip_Id | Report_Id
-----------------
    6   |    1
    4   |    2
    5   |    2
    2   |    2
    1   |    3

InfoProviders表

Ip_Id | Ip_Name
-----------------
    6   |    Comission
    4   |    Comapny
    5   |    Others
    2   |    People
    1   |    Traveler

多对多表QueriesIpRel

Ip_Id | Query_Id
-----------------
    6   |    3
    4   |    3
    5   |    3
    2   |    5
    1   |    1

和查询表

Query_Id | Query_Name
-----------------
    1   |    connection
    2   |    distantcon
    3   |    shortconn
    4   |    linking
    5   |    grounding

我试图实现的选择如下所示：

Report_Id | Report_name | Ip_Id | Ip_Name | Query_Id | Query_Name
    2     | Outcomes    |  4    | Comapny |  3       | shortconn
    2     | Outcomes    |  5    | Others  |  3       | shortconn
    2     | Outcomes    |  2    | People  |  5       | grounding

我尝试了许多像这样的左/内连接：

left outer join ReportsIPRel rir on r.Report_Id = rir.Report_Id
left outer join InfoProvider ipr on rir.Ip_Id = ipr.Ip_Id
left outer join QueriesIPRel qir on ipr.Ip_Id = qir.Ip_Id 
left outer join Queries q on qir.Query_Id = q.Query_Id
where r.Report_Id= '2'

但这一切都是徒劳的。

我必须使用where条件: WHERE report_Id = '2‘/EXAMPLE

我正尝试在SQL Server Management Studio中的ms SQL中执行此操作。

提前谢谢你

Answer 1

请尝试此查询，它在SQL server中对我有效：

Demo

select r.*, ipr.ip_id, ipr.Ip_Name, q.uery_Id, q.Query_Name from
reports r
left outer join ReportsIPRel rir on r.Report_Id = rir.Report_Id
left outer join InfoProviders ipr on rir.Ip_Id = ipr.Ip_Id
left outer join QueriesIPRel qir on ipr.Ip_Id = qir.Ip_Id 
left outer join Queries q on qir.Query_Id = q.uery_Id
where r.Report_Id= '2'