从左外连接获取不同的行

I am building an application which dynamically generates sql to search for rows of a particular Table (this is the main domain class, like an Employee).

我正在构建一个动态生成sql的应用程序来搜索特定表的行（这是主域类，如Employee）。

There are three tables Table1, Table2 and Table1Table2Map. Table1 has a many to many relationship with Table2, and is mapped through Table1Table2Map table. But since Table1 is my main table the relationship is virtually like a one to many.

Table1，Table2和Table1Table2Map有三个表。 Table1与Table2有多对多的关系，并通过Table1Table2Map表进行映射。但由于Table1是我的主要表格，因此这种关系几乎就像一对一。

My app generates a sql which basically gives a result set containing rows from all these tables. The select clause and joins dont change whereas the where clause is generated based on user interaction. In any case I dont want duplicate rows of Table1 in my result set as it is the main table for result display. Right now the query that is getting generated is like this:

我的应用程序生成一个sql，它基本上给出了一个包含所有这些表中的行的结果集。 select子句和连接不会更改，而where子句是基于用户交互生成的。在任何情况下，我都不希望在我的结果集中重复使用Table1，因为它是结果显示的主表。现在，生成的查询是这样的：

select distinct Table1.Id as Id, Table1.Name, Table2.Description from Table1
left outer join Table1Table2Map on (Table1Table2Map.Table1Id = Table1.Id)
left outer join Table2 on (Table2.Id = Table1Table2Map.Table2Id)

For simplicity I have excluded the where clause. The problem is when there are multiple rows in Table2 for Table1 even though I have said distinct of Table1.Id the result set has duplicate rows of Table1 as it has to select all the matching rows in Table2.

为简单起见，我已经排除了where子句。问题是Table2中Table1中有多行，即使我已经说明了Table1.Id，结果集也有重复的Table1行，因为它必须选择Table2中所有匹配的行。

To elaborate more, consider that for a row in Table1 with Id = 1 there are two rows in Table1Table2Map (1, 1) and (1, 2) mapping Table1 to two rows in Table2 with ids 1, 2. The above mentioned query returns duplicate rows for this case. Now I want the query to return Table1 row with Id 1 only once. This is because there is only one row in Table2 that is like an active value for the corresponding entry in Table1 (this information is in Mapping table). Is there a way I can avoid getting duplicate rows of Table1.

为了详细说明，请考虑对于Table1中Id = 1的行，Table1Table2Map（1,1）和（1,2）中有两行将Table1映射到Table2中的两行，其中ID为1,2。上述查询返回这种情况下重复的行。现在我希望查询只返回Id 1一次的Table1行。这是因为Table2中只有一行类似于Table1中相应条目的活动值（此信息在Mapping表中）。有没有办法可以避免获得Table1的重复行。

I think there is some basic problem in the way I am trying to solve the problem, but I am not able to find out what it is. Thanks in advance.

我认为我试图解决问题的方式存在一些基本问题，但我无法弄清楚它是什么。提前致谢。

6 个解决方案

#1

Try:

尝试：

left outer join (select distinct YOUR_COLUMNS_HERE ...) SUBQUERY_ALIAS on ...

In other words, don't join directly against the table, join against a sub-query that limits the rows you join against.

换句话说，不要直接加入表，加入一个限制你加入的行的子查询。

#2

You can use GROUP BY on Table1.Id ,and that will get rid off the extra rows. You wouldn't need to worry about any mechanics on join side.

您可以在Table1.Id上使用GROUP BY，这将消除额外的行。你不需要担心加入方面的任何机制。

I came up with this solution in a huge query and it this solution didnt effect the query time much.

我在一个巨大的查询中提出了这个解决方案，这个解决方案并没有太多影响查询时间。

NOTE : I'm answering this question 3 years after its been asked but this may help someone i believe.

注意：我在被问到3年后就回答了这个问题，但这可能对我相信的人有所帮助。

#3

You can re-write your left joins to be outer applies, so that you can use a top 1 and an order by as follows:

您可以将左连接重写为外部适用，以便您可以按如下方式使用前1和顺序：

select Table1.Id as Id, Table1.Name, Table2.Description 
from Table1
outer apply (
   select top 1 *
   from Table1Table2Map
   where (Table1Table2Map.Table1Id = Table1.Id) and Table1Table2Map.IsActive = 1
   order by somethingCol 
) t1t2
outer apply (
   select top 1 *
   from Table2
   where (Table2.Id = Table1Table2Map.Table2Id)
) t2;

Note that an outer apply without a "top" or an "order by" is exactly equivalent to a left outer join, it just gives you a little more control. (cross apply is equivalent to an inner join).

请注意，没有“top”或“order by”的外部应用完全等同于左外部连接，它只是为您提供更多控制。 （交叉申请相当于内部联接）。

You can also do something similar using the row_number() function:

你也可以使用row_number（）函数做类似的事情：

 select * from (
      select distinct Table1.Id as Id, Table1.Name, Table2.Description,
        rowNum = row_number() over ( partition by table1.id order by something )
      from Table1
      left outer join Table1Table2Map on (Table1Table2Map.Table1Id = Table1.Id)
      left outer join Table2 on (Table2.Id = Table1Table2Map.Table2Id)
 ) x
 where rowNum = 1;

Most of this doesn't apply if the IsActive flag can narrow down your other tables to one row, but they might come in useful for you.

如果IsActive标志可以将您的其他表缩小到一行，那么大部分都不适用，但它们可能对您有用。

#4

To elaborate on one point: you said that there is only one "active" row in Table2 per row in Table1. Is that row not marked as active such that you could put it in the where clause? Or is there some magic in the dynamic conditions supplied by the user that determines what's active and what isn't.

详细说明一点：你说Table1中每行的表2中只有一个“活动”行。该行未标记为活动，以便您可以将其放在where子句中吗？或者在用户提供的动态条件中有一些魔力可以决定什么是活动的，什么不是活动的。

If you don't need to select anything from Table2 the solution is relatively simply in that you can use the EXISTS function but since you've put TAble2.Description in the clause I'll assume that's not the case.

如果您不需要从Table2中选择任何内容，解决方案相对简单，因为您可以使用EXISTS函数，但由于您已将TAble2.Description放在子句中，我将假设情况并非如此。

Basically what separates the relevant rows in Table2 from the irrelevant ones? Is it an active flag or a dynamic condition? The first row? That's really how you should be removing duplicates.

基本上是什么将Table2中的相关行与不相关的行分开？它是一个活跃的旗帜还是一个动态的条件？第一排？这就是你应该如何删除重复项。

DISTINCT clauses tend to be overused. That may not be the case here but it sounds like it's possible that you're trying to hack out the results you want with DISTINCT rather than solving the real problem, which is a fairly common problem.

DISTINCT条款往往被过度使用。这可能不是这里的情况，但听起来你有可能试图用DISTINCT来破解你想要的结果，而不是解决真正的问题，这是一个相当普遍的问题。

#5

If you want to display multiple rows from table2 you will have duplicate data from table1 displayed. If you wanted to you could use an aggregate function (IE Max, Min) on table2, this would eliminate the duplicate rows from table1, but would also hide some of the data from table2.

如果要在table2中显示多行，则会显示来自table1的重复数据。如果你想在table2上使用聚合函数（IE Max，Min），这将消除table1中的重复行，但也会隐藏table2中的一些数据。

See also my answer on question #70161 for additional explanation

有关其他说明，另请参阅我对问题＃70161的回答

#6

You have to include activity clause into your join (and no need for distinct):

您必须在您的联接中包含activity子句（并且不需要不同）：

select Table1.Id as Id, Table1.Name, Table2.Description from Table1
left outer join Table1Table2Map on (Table1Table2Map.Table1Id = Table1.Id) and Table1Table2Map.IsActive = 1
left outer join Table2 on (Table2.Id = Table1Table2Map.Table2Id)