Left join

Dmitry Y
Дата: 12.02.2013 18:07:00
Все доброго времени суток.
Вроде не первый раз работаю с мусклем, но появился вопрос:
У меня есть таблица (к примеру перечень предметов). Вот я хочу вывести весь этот перечень предметов без повторений а к нему добавить столбцы из других таблиц (например характеристики) причём может быть такая ситуация, когда предмет есть, а характеристика к нему отсутствует, но при этом вывод должен осуществится всё равно (пусть с ячейкой Нулл). До сих пор был уверен что Left Join добавляет справа столбец для вывода и не фильтрует строки и был приятно удивлён, когда сделал так:
SELECT N_ID, TN_Name, TN_ID
FROM tovarname
Left Join nakladnye as n1 on n1.N_TN_ID=2 and n1.N_skl2=6 
Left Join provodka as p1 on p1.P_TN_ID=TN_ID and p1.P_N_ID=n1.N_ID

В итоге получаю 6 строк (именно 6 строк есть с n1.N_TN_ID=2 and n1.N_skl2=6) хотя в таблице tovarname около 100 записей. Путём исключения последней строки понял, что "фильтрует" вывод на экран именно "Left Join nakladnye as n1 on n1.N_TN_ID=2 and n1.N_skl2=6", хотя в моём представлении должно было вывалится 100 записей и из них только 6 строк с заполненными данными а остальные Нулл. Подскажите где грабли? Или Left join не пройдёт? Тогда чем?
Dmitry Y
Дата: 12.02.2013 18:16:49
Вопрос снят. Как всегда 30 минут мучения ничего не дают. Пишешь о помощи. Естественно ищешь проблему дальше и за 5 минут находишь решение до того как тебе помогли :)
Dmitry Y
Дата: 12.02.2013 19:47:28
Вообщем всё-равно затык. Понимаю что запрос будет не простой и с кучей вложений. Посему подскажите как лучше сделать. Вообщем есть таблица наименований товаров(TN_ID, TN_Name,...), таблица накладных (N_ID, N_SklFrom, N_SklTo,...) и таблица непосредственно товаров(P_ID, P_N_ID, P_TN_ID, P_SN, P_Kol, P_Cost,...). Задача: вывести весь список наименований товаров с дополнительными полями: кол-во товара на складе согласно его ID. Кол-во необходимо посчитать из таблицы товаров (где просуммировать поле Kol). Таблица накладных необходима для того, что бы выборку делать по выбранным складам:
где связи следующие:
P_TN_ID=TN_ID и P_N_ID=N_ID.
Внутреннее я подсказывает, что конструкция должна быть что-то типа
Select ... From ... Left Join ... on ID=(Select ...) ... Where

Но мозги за сутки под размякли и думать отказываются :)
Dmitry Y
Дата: 12.02.2013 20:08:17
Пока написал такой запрос:
SELECT TN_ID, TN_Name, p1.P_ESN, p1.P_Price, p1.P_Cost/p1.P_Kol as P1Cost, p1.P_TN_ID, p1.P_Kol, n1.N_ID as n1ID, p1.P_N_ID as p1NID, n1.N_Skl1, n1.N_Skl2
, p2.P_ESN, p2.P_Price, p2.P_Cost/p2.P_Kol as P1Cost, p2.P_TN_ID, p2.P_Kol, n2.N_ID as n2ID, p2.P_N_ID as p2NID, n2.N_Skl1, n2.N_Skl2
FROM tovarname
Left Join provodka as p1 on p1.P_TN_ID=TN_ID
Left Join nakladnye as n1 on n1.N_ID=p1.P_N_ID 
Left Join provodka as p2 on (p2.P_TN_ID=p1.P_TN_ID or p2.P_TN_ID is Null) and (p2.P_IMEI=p2.P_IMEI or p2.P_IMEI is Null)
Left Join nakladnye as n2 on (n2.N_ID=p2.P_N_ID  or n2.N_ID is Null)
Where TN_Type in (0,1)
and (n1.N_skl2=2 or n1.N_skl2 is null)
and (n2.N_skl1=2 or n2.N_skl1 is null)

Результат почти то что нужно: по приходу отображает все как нужно, а по расходу одно из двух:
1. Если расхода не было то напротив всех приходов пишется в расход 1ая позиция прихода (это видно из ЕСН)
2. Если расход был - то напротив всех приходов пишется позиция расхода (опять по ЕСН видно)
А необходимо, если расхода не было получить ячейку Нулл или 0
tanglir
Дата: 13.02.2013 07:20:45
Какой смысл в
Dmitry Y
Left Join provodka as p2 on (p2.P_TN_ID=p1.P_TN_ID or p2.P_TN_ID is Null)
? К каждой строке левой таблицы присоединить из правой не только совпадающие по ид, но ещё и все нулл-строки?
Dmitry Y
где просуммировать поле Kol
Ну и где вы суммируете?

А вообще из вашего описания задачи
Dmitry Y
Задача: вывести весь список наименований товаров с дополнительными полями: кол-во товара на складе согласно его ID.
вырисовывается нечто такое:
селект товар.всё_что_надо, агрегат.всего
фром товар лефт джойн (
 (селект идтовар, сумма(кол-во) всего
 фром {накладные и проводки, сджойните сами}
 груп бай идтовар
 вере {какие там вам нужны условия - склад, дата,...})
) агрегат он товар.ид=агрегат.идтовар
Dmitry Y
Дата: 06.03.2013 17:51:45
Вопрос остаётся открытым. Мне не понятно почему если я к таблице Товарнейм где TN_ID ключевое поле (т.е. не повторяется априори) присоединяю таблицу Проводка, где поле P_TN_ID=TN_ID повторяется, например, 5 раз. В результате получаю не столбец с сумой P_Kol, а 5 строк в которых P_Kol отдельно указаны. В моём понимании строка с TN_ID должна быть только 1 (так как мы перебираем Товарнейм), а справа сумма P_Kol по этому TN_ID.
SELECT TN_ID, TN_Name, p1.P_ESN, SUM(p1.P_Kol) as P1_Kol, SUM(p2.P_Kol) as P2_Kol
FROM tovargroup, tovarname
Left Join provodka as p1 on p1.P_TN_ID=TN_ID
Left Join nakladnye as n1 on n1.N_ID=p1.P_N_ID
Left Join provodka as p2 on p2.P_TN_ID=TN_ID
Left Join nakladnye as n2 on n2.N_ID=p2.P_N_ID and n2.N_TN_ID=2
Where TN_Type in (0,1) and (n1.N_skl2 in (2))
and p1.P_TN_ID in (Select P_TN_ID From nakladnye, provodka Where P_N_ID=N_ID and N_Skl1 in (2))
and p1.P_TN_ID=p2.P_TN_ID and n2.N_skl1 in (2) and p2.P_N_ID=n2.N_ID and p1.P_IMEI=p2.P_IMEI
and TG_ID=TN_TG_ID and n1.N_Skl2=n2.N_Skl1
Group by TN_ID, p1.P_ESN
qwerty112
Дата: 06.03.2013 18:24:43
Dmitry Y,

тебе всё станет понятно, если посмотришь на результат этого запроса БЕЗ группировки и агреггирования,
и к этому - нужно "уяснить" что группировка/агреггирование делаются в посл.очередь, т.е. вот как раз к тому результату, который ты будешь видеть без группировки

а выход - "простой как мычание" - джойнить УЖЕ сгруппированные "подзапросы"
Dmitry Y
Дата: 06.03.2013 18:39:33
А абсурдность заключается в том, что если отдельно делать запросы поступления и расхода получаем то что нужно:
SELECT DISTINCT TN_ID, TN_Name, p1.P_ESN, p1.P_ID, SUM(p1.P_Kol) as P1_Kol
FROM nakladnye as n1, tovargroup, tovarname
left Join provodka as p1 on TN_ID=p1.P_TN_ID
Where TN_Type in (0) and (n1.N_skl2 in (2)) and n1.N_ID=p1.P_N_ID
and p1.P_TN_ID in (Select P_TN_ID From nakladnye, provodka Where P_N_ID=N_ID and N_Skl1 in (2))
and TG_ID=TN_TG_ID
Group by TN_ID

и
SELECT TN_ID, TN_Name, p2.P_ESN, p2.P_ID, SUM(p2.P_Kol) as P1_Kol
FROM nakladnye as n2, tovargroup, tovarname
Left Join provodka as p2 on TN_ID=p2.P_TN_ID
Where TN_Type in (0) and (n2.N_skl1 in (2)) and n2.N_ID=p2.P_N_ID
and p2.P_TN_ID in (Select P_TN_ID From nakladnye, provodka Where P_N_ID=N_ID and N_Skl2 in (2))
and TG_ID=TN_TG_ID
Group by TN_ID

Но сразу вопрос: по всем канонам у меня должны выдаться все товары из tovarname и кол-во не нул в случае если товар был в накладных, и нул если не был. А у меня результат только те товары, которые были в накладных. Смена left на right тоже не помогла.
Ну и естественно при объединении запросов в один получаем вообще что-попало! Как сделать, что бы получить таблицу типа:
Id1 | Kol1_1 | Kol2_1
Id2 | Kol1_2 | Null
Id3 | Null | Kol2_3
Id4 | Null | Null
Id5 | Kol1_5 | Kol2_5
...
Idn | Kol1_n | Kol2_n
Dmitry Y
Дата: 06.03.2013 18:40:57
qwerty112,

1. Естественно я пробую без групировок
2. А как джойнить группировки?
qwerty112
Дата: 06.03.2013 19:01:53
Dmitry Y
Но сразу вопрос: по всем канонам у меня должны выдаться все товары из tovarname и кол-во не нул в случае если товар был в накладных, и нул если не был. А у меня результат только те товары, которые были в накладных. Смена left на right тоже не помогла.

условие в WHERE по правой таблице в LEFT OUTER JOIN делает из OUTER JOIN - INNER JOIN
тоже, с заменой лево/право справедливо и для RIGHT OUTER JOIN