Составить сложный запрос

[Joiner]

Здравствуйте, уважаемые форумчане!
Дублирую вопрос. Пробовал в ветке баз данных, результат нулевой, а время поджало. Вчера еще надо было.
Суть такова.
Есть база с таблицами работ- 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)

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)

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, чтобы выровнять, а толку нет - появляются повторяющиеся строки материалов.

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

Добавлено через 11 минут и 8 секунд:
Пример забыл подцепить.

[RXL]

От перемены мест результат не изменится.

https://forum.shelek.ru/index.php/topic,30633.0.html
Что в этой теме не так?

[Joiner]

Ну, тут, вроде, НЕОТЛОЖКА. А состояние уже критическое, скоро взрыв будет. Неделю тыркаюсь. Учителей нет, самоучкой. И со свободным временем очень плохо. Не успеваю изучить вопрос.

[RXL]

Быстрее не будет. Предлагаю слить это в одну тему.

[Joiner]

Спасибо. Топики одинаковые, может лучше удалить один. Какой не знаю. Оставьте пожалуйста, где, по Вашему мнению, лучше будет.

[Sla]

Рисуйте картинку, где что должно быть

[Joiner]

Вроде все подробно описал.

Не пойму как картинки вставить в текст. Прикрепил.

На первой, как это примерно выглядит.
На второй - запрос с ошибками, надо, чтобы список материалов был весь и без повторений по наименованию, а в присоединенных столбцах расход только по одной работе.

[Aether]

Я думаю, Sla имел ввиду не эти картинки, а описание цели, структуры и связей.

Если цель сформировать каталог услуг и материалов, то можно вообще обойтись одной таблицей для всего, сделать поле булевого типа для пояснения о чём речь о цене материала или услуги и всё.

Если цель формировать заказы, то таблиц лучше сделать три: на описание заказов, на цену работ и на материалы. Например:
Заказ ID 1 :
Грунтовка пола 10кв м по 15р за кв м = 150р
(Расходные материалы : грунт, кисть, ведро...)
Укладка плитки 5кв м по 24р за кв м = 120...

Если цель - управление складом, то тут сложнее, и больше вариантов для творчества.

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

Добавлено через 15 минут и 8 секунд:
Ещё, нюансы:
Почему там где измерение идёт в шт. использовано дробное число? У каждого материала должна быть стандартная упаковка для списания. Например, мешок цемента бывает 25, 40, 50кг. Если в работе нужно 15кг, то в расход нужно поместить 1шт. 25кг. Если нужно для работ 30кг, то списать надо 40кг 1шт. Дальше необходимо комбинировать, чтобы добиваться лучшей материалоёмкости.
В описании габаритов лучше использовать не знак "*", а "х" - привычнее прорабам и лучше читается на бумаге, например, 2х1,5х3,5, а не 2*1,5*3,5.

[Joiner]

Ребята, я, конечно, правила почитал и понял, что Вы добрые и ленивые. Но у меня в стартовом топике все подробно описано, и структура таблиц, и связи. А цель блока программы - это настройки.
Пользователь перемещается по списку работ вверху, а внизу появляется полный список материалов. Он может перейти вниз и выбрать материал который он хочет привязать к работе (это происходит в столбце Расход (_Rashod)) . При этом, естественно, он должен видеть привязки только к той работе, которую он выбрал, а список материалов, должен быть весь, чтобы он мог выбрать нужный материал из списка. Надеюсь понятно донес.    

Добавлено через 6 минут и 42 секунды:
По поводу единиц измерения Вы не переживайте. Во-первых, это я ткнул куда глаза глядят. Мне не важно что там выбралось. Мне важно, что бы правильно вывелось на форму. Во-вторых, там есть и канистры и рулоны и штуки, которые считаются долями на площадь. Это проблема сметчика, он может написать полбанки на кв.м. Они в чем покупают в том и меряют.

[Aether]

Цель: фильтр для таблицы материалов по видам работ.
Как связаны между собой обе таблицы? В чём суть WorkMat?
Можешь сделать кадр запроса по всем таблицам, оформить соответственно:
Инструкция: DESCRIBE smf_settings;
Результат:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| variable | varchar(255) | NO   | PRI |         |       |
| value    | text         | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Инструкция: SELECT variable, value FROM smf_settings;

+------------------------------+-----------------------------------------+
| variable                     | value                                   |
+------------------------------+-----------------------------------------+
| smfVersion                   | 2.0.11                                  |
| news                         | SMF - Just Installed!                   |
| compactTopicPagesContiguous  | 5                                       |
| compactTopicPagesEnable      | 1                                       |
| enableStickyTopics           | 1                                       |
| todayMod                     | 1                                       |
| karmaMode                    | 0                                       |
| karmaTimeRestrictAdmins      | 1                                       |
| enablePreviousNext           | 1                                       |
| pollMode                     | 1                                       |
| enableVBStyleLogin           | 1                                       |

Тут важны не все поля, но значащие. Ну и строки не все, но достаточные, чтобы потом понять то, как лучше связать всё это.

[Joiner]

Связь между Work и Mat многие-ко-многим. Для работы может быть несколько материалов. И материал может быть использован для нескольких работ.  Суть таблицы WorkMat - обеспечить эту связь. Мне нужен запрос, который выводит всю таблицу материалов из Mat и расход только для одной работы из таблицы WorkMat. И этот расход должен быть в строке соответствующего материала. Таблица Work в запросе не учавствует, она нужна, чтобы получить ID работы для запроса. Я ее для понимания сути показал.

Добавлено через 18 минут и 41 секунду:
Вот еще для понимания.

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

Код: (SQL)

SELECT * FROM WorkMat WHERE WorkMat.ID_Work=2;

Делаем следующий запрос

Код: (SQL)

SELECT Mat.*, QQQ.*
FROM Mat LEFT JOIN QQQ ON Mat.ID_Mat = QQQ.ID_Mat;

Как объединить два этих запроса в один.

[Aether]

Что из себя будет представлять QQQ физически?

[Joiner]

В Access это запрос и ему можно присвоить имя, потом использовать в другом запросе. А как физически, даже не задумывался, думаю так и хранится в виде строки запроса, просто результат для пользователя показывается в виде таблицы. В этом то и беда. В Access его нужно перед использованием обновлять, либо в ручку запускать, либо в макросе, если программно использовать. А вот как из внешней программы достать не знаю. А мне как раз из внешней надо. Есть, конечно, обходные пути. Сделать запрос, результат в буфер, потом программно расставить все по местам и вывести на экран,  но не хочется огород городить.

[Aether]

Суть в том, что таблицу связей можно реализовать по разному, например, сделать два столбца, один с ID работы, а другой типа строки переменной длины и туда записывать перечень ID материалов. Можно, вместо строки использовать BLOB - двоичные массивы, можно пробовать двумерный массив... Эти детали реализации важны для правильного понимания и соответственно для правильного составления запроса.
Не знаю про Access, а так в SQL можно использовать временные переменные и временные таблицы для хранения промежуточного результата. В некоторых версиях доступен вложенный запрос. Существует работа со строками.
Может быть вообще пересмотреть систему - добавить столбцы флагов материала в таблицу работ или строку с перечислением ID материалов. Соответственно, программа запрашивает у БД перечень материалов для конкретного вида работ, потом формирует запрос на материалы. "Короткий путь - чаще тот, который знаешь."

Добавлено через 1 минуту и 32 секунды:
Вот, для размышления:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);


INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());



SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

[Joiner]

Как использовать WHERE со сложными фильтрами я прекрасно представляю.
Хорошо попробую сформулировать абстрактно по другому. Есть две таблицы со связью один-к-многим. Мне нужно объединить их в одну. Используем LEFT JOIN. Левая (таблица1) целиком без изменений, а в правой (таблица2), естественно, записи будут подцеплятся по условию, что сходятся ключи в выражении ON. Проблема в том,что мне надо чтобы кроме совпадения ключей для второй таблицы выполнялось еще одно условие, например Таблица2.поле1=2. LEFT JOIN не позволяет ставить дополнительное условие.   

Добавлено через 24 минуты и 5 секунд:
В моем случае и в простейшем варианте

Код: (SQL)

SELECT Mat.*, WorkMat.*
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat

Что и где мне здесь пределать, чтобы в выражении LEFT JOIN дополнительно отфильтровало из WorkMat строки с ID_Work=2.

[Aether]

Для обработки по условию, как раз и нужно WHERE.

Если предположить, что WorkMat - список вида:
ID, ID_Mat, ID_Work.

Что мешает:
SELECT * FROM Mat, WorkMat WHERE Mat.ID_Mat = WorkMat.ID_Mat AND WorkMat.ID_Work = 2;

[Joiner]

Тогда я получу, только записи, где ID_Work=2. А мне нужно, чтобы таблица Mat была вся прився прився. Поэтому я и использую внешнее присоединение  LEFT JOIN. Это еще называется горизонтальное объединение, когда к одной таблице подцепляются столбцы из другой таблицы по общему ключу. Только нужно из второй таблицы отфильтровать записи, что бы ID_Work=2.

Добавлено через 14 минут и 43 секунды:

Суть в том, что таблицу связей можно реализовать по разному, например, сделать два столбца, один с ID работы, а другой типа строки переменной длины и туда записывать перечень ID материалов. Можно, вместо строки использовать BLOB - двоичные массивы, можно пробовать двумерный массив... Эти детали реализации важны для правильного понимания и соответственно для правильного составления запроса.
Не знаю про Access, а так в SQL можно использовать временные переменные и временные таблицы для хранения промежуточного результата. В некоторых версиях доступен вложенный запрос. Существует работа со строками.
Может быть вообще пересмотреть систему - добавить столбцы флагов материала в таблицу работ или строку с перечислением ID материалов. Соответственно, программа запрашивает у БД перечень материалов для конкретного вида работ, потом формирует запрос на материалы. "Короткий путь - чаще тот, который знаешь."

Тут дело не в Access, я просто использую его как базу данных для программы. Я уже в принципе подумывал, чтобы делать запрос с фильтром, записать результат во временную таблицу, а потом уже присоединять эту временную таблицу. В этих таблицах будет строк по 500. Это не беда. А вот дальше будет продолжение программы, где таких строк в других таблицах будет много. А нужно будет сделать похожие запросы.  Очень не хочется городить такой огород. Как вариант можно вообще сбросить все в listbox и там отсортировать и отфильтровать. Но думаю есть нормальное решение одним запросом и все.

[RXL]

Joiner, т.е. ты хочешь получить именно Mat и подтянуть строки одной работы?

FROM Mat m LEFT JOIN WorkMat wm ON wm.ID_Mat = m.ID_Mat AND wm.ID_Work = 2

[Joiner]

Joiner, т.е. ты хочешь получить именно Mat и подтянуть строки одной работы?

FROM Mat m LEFT JOIN WorkMat wm ON wm.ID_Mat = m.ID_Mat AND wm.ID_Work = 2

такая конструкция "ON wm.ID_Mat = m.ID_Mat AND wm.ID_Work = 2" не прокатывает я пробовал. Ругается что нельзя в JOIN использовать объединение.

Добавлено через 2 минуты и 1 секунду:
Но слава богу хоть нашелся человек, который понял суть.

[RXL]

Просто у тебя необычный подход.

Ты борешься не с SQL, а с Access. Смотри его доку, что он позволяет, а что нет. Если не позволяет задать wm.ID_Work = 2 в LEFT JOIN, попробуй через подзапрос (если Access это умеет):

FROM Mat m LEFT JOIN (SELECT * FROM WorkMat WHERE wm.ID_Work = 2) wm ON wm.ID_Mat = m.ID_Mat

[Joiner]

Так я пробовал. Access подзапросы поддерживает. Но в Access какая-то заморочка с алиасами. По мануалу вроде есть, но по другому объявляется, типа SELECT Mat AS M.  Предложенную Вами конструкцию должен проглатывать, но выдает ошибку в JOIN. Я решил, что что-то не так делаю. Спасибо за ответ.

Добавлено через 45 минут и 51 секунду:
Спасибо RXL

Код: (SQL)

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];

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

[RXL]

AS для алиасов - штука стандартная, но необязательная в большинстве SQL-диалектов.

[HandKot]

Код:

SELECT 
	SetMat.ID_Mat
	, SetMat.[_Name]
	, SetMat.[_UnitMat]
	, SetMat.[_Price]
	, SetMat.[_Group]
	, SetMat.[_Free]
	, SetMat.[_Select]
	, SetWorkMat.[_Rashod]
	, SetWorkMat.ID_Work
FROM 
	SetMat 
INNER JOIN SetWorkMat ON SetMat.ID_Mat = SetWorkMat.ID_Mat
UNION ALL
SELECT 
	SetMat.ID_Mat
	, SetMat.[_Name]
	, SetMat.[_UnitMat]
	, SetMat.[_Price]
	, SetMat.[_Group]
	, SetMat.[_Free]
	, SetMat.[_Select]
	, 0
	, SetWork.ID_Work
FROM 
	SetWork, SetMat
WHERE
	NOT EXISTS (SELECT * FROM SetWorkMat WHERE SetWorkMat.ID_Work = SetWork.ID_Work AND SetMat.ID_Mat = SetWorkMat.ID_Mat)