Получение отдельных строк из левого внешнего соединения

Я создаю приложение, которое динамически генерирует sql для поиска строк конкретной таблицы (это основной класс домена, например Employee).

Существуют три таблицы Table1, Table2 и Table1Table2Map. Таблица 1 имеет много-много отношений с таблицей 2 и отображается в таблице Table1Table2Map. Но так как Table1 является моей основной таблицей, отношения практически одинаковы друг от друга.

Мое приложение генерирует sql, который в основном дает набор результатов, содержащий строки из всех этих таблиц. Предложение select и joins не изменяются, тогда как предложение where создается на основе взаимодействия пользователя. В любом случае я не хочу дублировать строки таблицы 1 в моем результирующем наборе, так как это основная таблица для отображения результатов. Сейчас запрос, который создается, выглядит следующим образом:

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)

Для простоты я исключил предложение where. Проблема заключается в том, что в таблице 2 для таблицы есть несколько строк, хотя я сказал, что отличается от Table1.Id набор результатов имеет повторяющиеся строки таблицы 1, поскольку он должен выбрать все соответствующие строки в таблице2.

Чтобы подробнее рассказать о том, что для строки в таблице 1 с Id = 1 в таблице Table1Table2Map (1, 1) и (1, 2) отображаются таблицы 1 в две строки в таблице 2 с идентификаторами 1, 2. Вышеуказанные упомянутый запрос возвращает повторяющиеся строки для этого случая. Теперь я хочу, чтобы запрос возвращал строку Table1 с идентификатором 1 только один раз. Это связано с тем, что в таблице 2 есть только одна строка, которая соответствует активному значению для соответствующей записи в таблице 1 (эта информация содержится в таблице сопоставления). Есть ли способ избежать дублирования строк таблицы.

Я думаю, что есть некоторые основные проблемы в том, как я пытаюсь решить проблему, но я не могу понять, что это такое. Спасибо заранее.

Ответ 1

Try:

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

Другими словами, не присоединяйтесь непосредственно к таблице, присоединяйтесь к подзапросу, который ограничивает строки, к которым вы присоединяетесь.

Ответ 2

Вы можете использовать GROUP BY на Table1.Id, и это избавит вас от лишних строк. Вам не нужно беспокоиться о каких-либо механиках на стороне присоединения.

Я придумал это решение в огромном запросе, и это решение не сильно повлияло на время запроса.

ПРИМЕЧАНИЕ. Я отвечаю на этот вопрос через 3 года после его запроса, но это может помочь кому-то, кому я верю.

Ответ 3

Вы можете переписать свои левые соединения как внешние, чтобы вы могли использовать верхний 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;

Обратите внимание, что внешнее приложение без "верхнего" или "порядка" точно эквивалентно левому внешнему соединению, оно просто дает вам немного больше контроля. (cross apply эквивалентно внутреннему соединению).

Вы также можете сделать что-то подобное с помощью функции 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;

Большая часть этого не применяется, если флаг IsActive может сузить ваши другие таблицы до одной строки, но они могут оказаться полезными для вас.

Ответ 4

Чтобы уточнить один момент: вы сказали, что в таблице 1 есть только одна "активная" строка в таблице2 для каждой строки. Является ли эта строка не помеченной как активная, чтобы вы могли поместить ее в предложение where? Или есть какая-то магия в динамических условиях, предоставляемых пользователем, который определяет, что активно, а что нет.

Если вам не нужно ничего выбирать из таблицы 2, решение относительно просто в том, что вы можете использовать функцию EXISTS, но поскольку вы указали TAble2.Description в предложении, я предполагаю, что это не так.

В основном, что отделяет соответствующие строки в таблице 2 от нерелевантных? Является ли это активным флагом или динамическим состоянием? Первая строка? Это действительно то, как вы должны удалять дубликаты.

Предложения DISTINCT, как правило, чрезмерно используются. Возможно, это не так, но кажется, что вы пытаетесь вырезать результаты, которые вы хотите, с помощью DISTINCT, а не решать настоящую проблему, что является довольно распространенной проблемой.

Ответ 5

Если вы хотите отобразить несколько строк из таблицы2, вы будете иметь дубликаты данных из отображаемой таблицы1. Если бы вы хотели, чтобы вы могли использовать агрегатную функцию (IE Max, Min) в таблице2, это устранило бы повторяющиеся строки из таблицы1, но также скрыть некоторые данные из таблицы2.

См. также мой ответ на вопрос #70161 для дополнительного объяснения

Ответ 6

Вы должны включить предложение активности в свое соединение (и не нужно различать):

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)