如何在SQL查询中链接多对多 [英] How do I link many to many in SQL query

问题描述

嗨大家好



我需要以下帮助



我有3个数据库表



A

B

C



表A和表B具有1对多的关系（Key_Table A =表B中的倍数）

表A和表C也具有1对多的关系（表C中的Key_Table A = Multiple）< br $>


我面临的问题是我需要编写一个查询来从表A，B，C中检索一些信息，例如：
。



从表A中选择Column1，column2，TableB.column1，TableC.column1





我如何加入这些表格，以便我不会重复记录？



我尝试了什么： < br $>


选择TableA.Column1，TableB.Column1，TableC.Column1



来自TableA

内部加入

TableA在TableA.Column_key = TableB.TableA_key

内部加入

Ta TableA上的bleB.Column_key = TableB.TableA_key

Hi Guys

I need some assistance with the below

I have 3 database tables

A
B
C

Table A and Table B have a 1 to many relationship(Key_Table A = Multiple in Table B)
Table A and Table C also have a 1 to many relationship(Key_Table A = Multiple in Table C)

The problem i am facing is that i need to write a query to retrieve some information from Table A,B,C
for eg.

Select Column1,column2,TableB.column1,TableC.column1
from Table A

How would i join these tables so that i wont get a duplication of records?

What I have tried:

Select TableA.Column1,TableB.Column1,TableC.Column1

From TableA
Inner Join
TableB on TableA.Column_key = TableB.TableA_key
Inner Join
TableB on TableA.Column_key = TableB.TableA_key

推荐答案

因此，您只需要TableC中其中一行的结果行，那么您必须为此添加条件。 br />

So you only want resulting rows from one of the rows in TableC, then you must add a condition for that.

WITH CTE AS (
    SELECT  TableA.KEY
           ,Max(TableC.Column1) AS Column1
    FROM    TableA
    JOIN    TableC ON TableA.Column_key = TableC.TableA_key
    GROUP BY TableA.KEY
    )
SELECT  CTE.KEY
       ,TableB.Column1
       ,CTE.Column1
FROM    CTE
JOIN    TableB ON CTE.KEY = TableB.TableA_key

这篇关于如何在SQL查询中链接多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！