Проблема с LEFT JOIN.

[beresa]

Уважаемые, помогите разобраться.
У меня в базе есть отношение таблиц многие-ко-многим (рис. 1). Так вот, с помощью внешнего левого соединения хотел бы вытащить людей (таблица PERSON), связанных (и не связанных) с определённой работой.
Для этого составляю запрос (выбираем людей, занимающихся вязанием):

Код: Выделить всё

SELECT
    a.id_person,
    a.vc_person,
    b.id_make
FROM
    person a LEFT JOIN make b ON
    a.id_person=b.id_person
WHERE   
    b.id_work=1 

В результате получается набор данных, состоящих из 4-ёх строк (см. рисунок). Почему нет ещё одной строки (должен быть Дмитриев, а в столбце id_wok должно по идее стоять null)?

Требуемый набор данных мне удалось получить с помощью подзапроса (см. рисунк):

Код: Выделить всё

SELECT
    a.id_person,
    a.vc_person,
    (SELECT b.id_make
    FROM make b
    WHERE a.id_person=b.id_person and b.id_work=1)
FROM
    person a 

Но в моём случае это не решение проблемы (т.к. в реальной базе значительно больше таблиц и столбцов).

Как всё-таки получить требуемый набор данных?
Почему не работает левое внешнее соединение?

[mdfv]

Как раз все отлично работает как и написано в запросе.
В условиях соединения никто не мешает ограничивающие условия ставить. А where работает уже поверх всего соединенного набора.

[WildSery]

Как всё-таки получить требуемый набор данных?
Почему не работает левое внешнее соединение?

Внимательно читать статью о JOINS на этом сайте, вникнуть в раздел "Отличие между ON и WHERE"

А where работает уже поверх всего соединенного набора.

Не совсем так. Однако, результат будет идентичен такому алгоритму.

[kdv]

кстати, что за фигня ID_MAKE ??? первичный ключ у таблицы MAKE должен состоять из двух столбцов - ID_PERSON и ID_WORK.

см. www.ibase.ru/devinfo/joins.htm

правда, сначала надо модели правильные строить...

[beresa]

Уважаемый kdv, спасибо большое за статью «Использование неявных и явных Join в InterBase и FireBird». Написано понятно и доступно. Прочитал и сразу «врубился», что для получения требуемого набора данных запрос нужно было строить следующим образом:

Код: Выделить всё

SELECT 
    a.id_person, 
    a.vc_person, 
    b.id_make 
FROM 
    person a LEFT JOIN make b ON 
    a.id_person=b.id_person and  b.id_work=1

Всё работает так как надо! А насчёт избыточности (ID_MAKE),- да, всё понял – первичный ключ действительно нужно составлять из ID_WORK и ID_PERSON. Спасибо за указание!

[ODIN]

если действительно сделать первичный ключ составным из ID_WORK и ID_PERSON то получится что "Александров" сможет один раз в жизни заняться "Вязание"м
моё мнение лучше оставить первичный ключ ID_MAKE или дорабатывать структуру... например извращаться дальше... делать первичный ключ из трёх...четырёх... и тд полей или накатывать unique constraint на необходимые поля

[kdv]

получится что "Александров" сможет один раз в жизни заняться "Вязанием"

в исходной задаче про "один раз в жизни" и т.п. не шло, так что не надо додумывать варианты развития модели за автора топика.

[ODIN]

тоды таблицу называть надо что то в стиле "профориентации сотрудников" (хотяб WORK_PERSON) а не таблица работ... а то получится как вы яхту назовёте так она и поплывёт

[WildSery]

а то получится как вы яхту назовёте так она и поплывёт

//OFF я б свою яхту назвал "говно". чтобы непотопляемая была.