Составить сложный запрос к связанным таблицам

@Joiner · Регистрация: 25.03.2014

Студворк — интернет-сервис помощи студентам

Здравствуйте, уважаемые форумчане!
Дублирую вопрос. Пробовал в ветке баз данных, результат нулевой, а время поджало. Вчера еще надо было.
Суть такова.
Есть база с таблицами работ- Work, материалов-Mat и привязки материалов к определенным работам -WorkMat. Связь между Mat и Work через WorkMat многие-ко-многим. Т.е. для некоторых работ есть материалы и любой материал может использоваться для разных работ. Пример в Access вложен с примером моего пробного запроса.

На форме две таблицы - вверху работы, внизу материалы. При переходе по строкам работ внизу должен появиться полный список ВСЕХ материалов без повторов и пропусков, а в присоединенных столбцах данные из WorkMat только для ОДНОЙ выбранной вверху работы. Пробовал и LEFT JOIN и UNION инструкции, то повторяющиеся записи появляются, то, наоборот часть записей пропадает. Пробовал сложные запросы конструировать, только больше запутался.
В сокращенном варианте база такая:

Поля Работы (Work) - ID_Work(ключ), Name, Union, Price
Поля Материалов (Mat) - ID_Mat(ключ), Name, Union, Price
Связывающая таблица (WorkMat) - ID_Work(ключ), ID_Mat(ключ), Rashod (Расход материала для конкретной работы)

Связь один-ко-многим Work.ID_Work -> WorkMat.ID_Work
Связь один-ко-многим Mat.ID_Mat -> WorkMat.ID_Mat

Реально в таблице Work и Mat полей больше, но, думаю, это не принципиально. Мне наводка на правильный запрос нужна. С полями, думаю, разберусь. Я пробовал комбинировать и объединение, и присоединение полей, ставил EXISTS, пробовал применять DISTINCT, но у меня либо повторение материала по названию происходит, либо не полный список материалов.

Запрос пока в таком виде:

SQL
1
2
3
4
SELECT Mat.ID_Mat, Mat.[Name], Mat.[Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat
WHERE WorkMat.ID_Work=2 OR WorkMat.ID_Work IS NULL
ORDER BY Mat.[Name];

При таком запросе выводятся не все строки материалов. Те, которые ID_Work<>2 не видны

Пробовал добавить:

SQL
1
2
3
4
UNION ALL
SELECT Mat.ID_Mat, Mat.[Name], Mat.[Unit]
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat
WHERE WorkMat.ID_Work<>2

Приходится добавлять пустые поля в таблицу Mat, чтобы выровнять, а толку нет - появляются повторяющиеся строки материалов.

Заранее благодарен.

@ovva · 20.09.2016, 00:30

Joiner, не знаю, насколько это вас устроит, но вот решение с использованием LinqToSQL.

VB.NET
1
2
3
4
5
6
7
8
9
10
Private Sub vmSelect(ByVal IDWork As Integer)
    Dim tbW As DataTable = DS.Tables("SetWork")
    Dim tbM As DataTable = DS.Tables("SetMat")
    Dim tbWM As DataTable = DS.Tables("SetWorkMat")
    Dim selWM() = (From ww As DataRow In tbW, mm As DataRow In tbM, wm As DataRow In tbWM _
             Where ww!ID_Work = IDWork AndAlso wm!ID_Work = IDWork AndAlso mm!ID_Mat = wm!ID_Mat _
             Select ww!ID_Work, ww!_NameWork, ww!_PriceWork, mm!ID_Mat, mm!_Name, mm!_Price, wm!_Rashod).ToArray
    DataGridView1.DataSource = Nothing
    DataGridView1.DataSource = selWM
End Sub

@Joiner · 20.09.2016, 00:45 **[ТС]**

Спасибо за ответ, только не так. В скрине как надо.
Матералы должны быть все, чтобы подключать или отключать.
А вот расход только по одной работе, которая вверху выделена.

@Kulma · 20.09.2016, 07:58

не открывается у меня в 2007 офисе ваша база
можно по-подробнее , что от этого запроса вы хотите получить ?

Сообщение от Joiner

T-SQL
1
2
3
4
SELECT Mat.ID_Mat, Mat.[Name], Mat.[Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat
WHERE WorkMat.ID_Work=2 OR WorkMat.ID_Work IS NULL
ORDER BY Mat

.[Name];

Ваши сетования напрасны

Сообщение от Joiner

При таком запросе выводятся не все строки материалов. Те, которые ID_Work<>2 не видны

Как же он вам выведет ID_Work<>2 , если поставлено жёсткое ограничение "=".
OR предполагает соответствие одному из условий WHERE.
Хотите чтобы выводились результаты по обоим условиям , пишите AND вместо OR.

@Joiner · 20.09.2016, 10:45 **[ТС]**

Спасибо, что взялись помочь.

Такой запрос выводит как на картинке pic3.

SQL
1
2
3
4
SELECT Mat.ID_Mat, Mat.[_Name], Mat.[_Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON WorkMat.ID_Mat=Mat.ID_Mat
WHERE WorkMat.ID_Work=2 OR WorkMat.ID_Work IS NULL
ORDER BY Mat.[_Name];

в таблице нет материала с ID = 3. Мне нужно, что бы был полный список материалов.

теперь пытаюсь добавить недостающие строки материалов делая объединение (на pic4)

SQL
1
2
3
4
5
6
7
8
SELECT Mat.ID_Mat, Mat.[_Name], Mat.[_Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON WorkMat.ID_Mat=Mat.ID_Mat
WHERE WorkMat.ID_Work=2 OR WorkMat.ID_Work IS NULL
UNION ALL 
SELECT Mat.ID_Mat, Mat.[_Name], Mat.[_Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON WorkMat.ID_Mat=Mat.ID_Mat
WHERE WorkMat.ID_Work<>2
ORDER BY Mat.[_Name];

теперь у меня дважды выходит "Выключатель" и, естественно, появился 'Rashod' для ID_Work=1
Как сделать, чтобы не было повторов, и чтобы 'Rashod' появился только для ID_Work=2.

@Joiner · 20.09.2016, 11:11 **[ТС]**

Добавляю к вышесказанному.
Я на сколько понял, мне надо сначала выдернуть из таблицы WorkMat строки где ID_Work=2, а потом присоединить то, что получилось к таблице 'Mat' (материалы). Двумя запросами я это делаю. Но мне нужно одним запросом. Как вложить один в другой. Как в LEFT JOIN вложить другой запрос, что бы выдернуть только с ID_Work=2/

@Kulma · 20.09.2016, 13:08

Сообщение от Joiner

в таблице нет материала с ID = 3. Мне нужно, что бы был полный список материалов.

Сделайте пожалуйста скрин промежуточной таблицы с данными
У вас там точно есть соотношение 2-3 , т.е. ID_Work=2 соотносится с ID_Mat=3 ?
Здесь

SQL
1
2
3
4
5
6
7
8
SELECT Mat.ID_Mat, Mat.[_Name], Mat.[_Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON WorkMat.ID_Mat=Mat.ID_Mat
WHERE WorkMat.ID_Work=2 OR WorkMat.ID_Work IS NULL
UNION ALL 
SELECT Mat.ID_Mat, Mat.[_Name], Mat.[_Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON WorkMat.ID_Mat=Mat.ID_Mat
WHERE WorkMat.ID_Work<>2
ORDER BY Mat.[_Name]

попробуйте использовать UNION , а не UNION ALL
ALL в данном операторе объединяет в результирующий набор все строки , включая дубликаты.
Если ALL не указан , то дубликаты должны отсекаться.

@Joiner · 20.09.2016, 13:46 **[ТС]**

Такой запрос дублирует записи, поскольку один материал может быть связан с несколькими работами. В данном случае ID_Work<>2 как раз и дублирует такие записи.

@Joiner · 20.09.2016, 13:54 **[ТС]**

вот скрин

@Kulma · 20.09.2016, 14:23

Ну собственно о чём я вам и говорил.
Откуда возьмутся в результате запроса материалы с ID 3,
если выборка идет по работам ID_work с ID2 , а , исходя из скрина в работах с этим ID
материалы с ID3 не используются .

По сложному запросу я что-то завис пока...никак не могу сообразить какой должен быть результат
(что из чего должно получиться)

@Joiner · 20.09.2016, 20:57 **[ТС]**

я это тоже понимаю, в этом то и суть моей проблемки. Мне надо, чтобы материалы из таблицы Mat были ВСЕ и без повторов. А вот присоединенные в JOIN столбцы из WorkMat содержали только записи для ID_Work=2.

Добавлено через 6 часов 28 минут
Кому интересно

SQL
1
2
3
SELECT Mat.*, WM.*
FROM Mat LEFT JOIN (SELECT WorkMat.* FROM WorkMat WHERE WorkMat.ID_Work=2) AS WM ON Mat.ID_Mat = WM.ID_Mat
ORDER BY Mat.[Name];

Вот такой запрос заработал так как надо.

Новые блоги и статьи Все статьи Все блоги /
BOINC: 22 года — и всё ещё работает Programma_Boinc 12.03.2026 BOINC: 22 года — и всё ещё работает Дэвид Андерсон написал ретроспективу. Кратко: в 2001 году он ушёл из United Devices, где был CTO, и за несколько месяцев написал ядро BOINC — клиент, сервер,. . .	SDL3 для Desktop (MinGW): Создаём пустое окно с нуля для 2D-графики на SDL3, Си и C++ 8Observer8 10.03.2026 Содержание блога Финальные проекты на Си и на C++: hello-sdl3-c. zip hello-sdl3-cpp. zip Результат:	Установка CMake и MinGW 13.1 для сборки С и C++ приложений из консоли и из Qt Creator в EXE 8Observer8 10.03.2026 Содержание блога MinGW - это коллекция инструментов для сборки приложений в EXE. CMake - это система сборки приложений. Здесь описаны базовые шаги для старта программирования с помощью CMake и. . .	Как дизайн сайта влияет на конверсию: 7 решений, которые реально повышают заявки Neotwalker 08.03.2026 Многие до сих пор воспринимают дизайн сайта как “красивую оболочку”. На практике всё иначе: дизайн напрямую влияет на то, оставит человек заявку или уйдёт через несколько секунд. Даже если у вас. . .
Модульная разработка через nuget packages DevAlt 07.03.2026 Сложившийся в . Net-среде способ разработки чаще всего предполагает монорепозиторий в котором находятся все исходники. При создании нового решения, мы просто добавляем нужные проекты и имеем. . .	Модульный подход на примере F# DevAlt 06.03.2026 В блоге дяди Боба наткнулся на такое определение: В этой книге («Подход, основанный на вариантах использования») Ивар утверждает, что архитектура программного обеспечения — это структуры,. . .	Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .

@Joiner 1 / 1 / 1 Регистрация: 25.03.2014 Сообщений: 67
	20.09.2016, 00:45 [ТС]
	Спасибо за ответ, только не так. В скрине как надо. Матералы должны быть все, чтобы подключать или отключать. А вот расход только по одной работе, которая вверху выделена. Миниатюры 0

@Joiner 1 / 1 / 1 Регистрация: 25.03.2014 Сообщений: 67
	20.09.2016, 11:11 [ТС]
	Добавляю к вышесказанному. Я на сколько понял, мне надо сначала выдернуть из таблицы WorkMat строки где ID_Work=2, а потом присоединить то, что получилось к таблице 'Mat' (материалы). Двумя запросами я это делаю. Но мне нужно одним запросом. Как вложить один в другой. Как в LEFT JOIN вложить другой запрос, что бы выдернуть только с ID_Work=2/ 0

@Joiner 1 / 1 / 1 Регистрация: 25.03.2014 Сообщений: 67
	20.09.2016, 13:46 [ТС]
	Такой запрос дублирует записи, поскольку один материал может быть связан с несколькими работами. В данном случае ID_Work<>2 как раз и дублирует такие записи. 0

@Joiner 1 / 1 / 1 Регистрация: 25.03.2014 Сообщений: 67
	20.09.2016, 13:54 [ТС]
	вот скрин Миниатюры 0

@Kulma 1730 / 1602 / 166 Регистрация: 25.07.2015 Сообщений: 2,676
	20.09.2016, 14:23
	Ну собственно о чём я вам и говорил. Откуда возьмутся в результате запроса материалы с ID 3, если выборка идет по работам ID_work с ID2 , а , исходя из скрина в работах с этим ID материалы с ID3 не используются . По сложному запросу я что-то завис пока...никак не могу сообразить какой должен быть результат (что из чего должно получиться) 0