join одной и той же таблицы, используя псевдонимы

[Solo]

Я все о своем
Есть главная таблица (с адресами). К ней придеталены 2 деталь-таблицы: одна с начислениями услуг за период, вторая - с проживающими по этому адресу за тот же период.

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

Пробуем использовать псевдонимы и директиву JOIN. В начале у меня выходили миллионы записей (хорошо IB_Expert дает возможность остановить фетч). Теперь поменьше, но все равно при объединении таблицы самой с собой происходит ПЕРЕМНОЖЕНИЕ. А это не есть гуд.

Вот мой запрос:

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

SELECT TM.ADRESS, NF.NAME_F, NF.KAT_NAME, NF.D_BORN, NF.DOCUM,
       O.SRV_NAME, HV.SRV_NAME, GV.SRV_NAME, HA.SRV_NAME, GA.SRV_NAME, M.SRV_NAME, SN.SRV_NAME,
       SB.SRV_NAME, E.SRV_NAME, LV.SRV_NAME, N.SRV_NAME
FROM THEMAIN TM join N_FAMILY NF on (nf.mid = tm.mid) and nf.kat_name containing 'Ветераны труда'
join nachisl O  on (O.mid =  tm.mid) and ((O.D_BEGIN  >= '01.01.2005') and  (O.D_END <= '31.01.2005'))
join nachisl HV on (HV.mid = tm.mid) and ((HV.D_BEGIN >= '01.01.2005') and (HV.D_END <= '31.01.2005'))
join nachisl GV on (GV.mid = tm.mid) and ((GV.D_BEGIN >= '01.01.2005') and (GV.D_END <= '31.01.2005'))
join nachisl HA on (HA.mid = tm.mid) and ((HA.D_BEGIN >= '01.01.2005') and (HA.D_END <= '31.01.2005'))
join nachisl GA on (GA.mid = tm.mid) and ((GA.D_BEGIN >= '01.01.2005') and (GA.D_END <= '31.01.2005'))
join nachisl M  on (M.mid  = tm.mid) and ((M.D_BEGIN  >= '01.01.2005') and (M.D_END  <= '31.01.2005'))
join nachisl SN on (SN.mid = tm.mid) and ((SN.D_BEGIN >= '01.01.2005') and (SN.D_END <= '31.01.2005'))
join nachisl SB on (SB.mid = tm.mid) and ((SB.D_BEGIN >= '01.01.2005') and (SB.D_END <= '31.01.2005'))
join nachisl E  on (E.mid  = tm.mid) and ((E.D_BEGIN  >= '01.01.2005') and (E.D_END  <= '31.01.2005'))
join nachisl LV on (LV.mid = tm.mid) and ((LV.D_BEGIN >= '01.01.2005') and (LV.D_END <= '31.01.2005'))
join nachisl N  on (N.mid  = tm.mid) and ((N.D_BEGIN  >= '01.01.2005') and (N.D_END  <= '31.01.2005'))

WHERE 
   ( (O.SRV_NAME = 'Отопление') or (O.SRV_NAME IS NULL ) ) and
   ( (HV.SRV_NAME = 'Холодная вода') or (HV.SRV_NAME IS NULL ) ) and
   ( (GV.SRV_NAME = 'Горячая вода') or (GV.SRV_NAME IS NULL ) ) and
   ( (HA.SRV_NAME = 'Ассенизация с холодной водой') or (HA.SRV_NAME IS NULL ) ) and
   ( (GA.SRV_NAME = 'Ассенизация с горячей водой') or (GA.SRV_NAME IS NULL ) ) and
   ( (M.SRV_NAME = 'Вывоз мусора') or (M.SRV_NAME IS NULL ) ) and
   ( (SN.SRV_NAME = 'Содержание жилья (неблагоустроенного)') or (SN.SRV_NAME IS NULL ) ) and
   ( (SB.SRV_NAME = 'Содержание жилья (благоустроенного)') or (SB.SRV_NAME IS NULL ) ) and
   ( (E.SRV_NAME = 'Электроэнергия') or (E.SRV_NAME IS NULL ) ) and
   ( (LV.SRV_NAME = 'Летний водопровод') or (LV.SRV_NAME IS NULL ) ) and
   ( (N.SRV_NAME = 'Наем') or (N.SRV_NAME IS NULL ) )

Перемножается. То-есть выходит одно и то же по нескольку раз. Хотя tm.MID - это идентификатор главной таблицы, где адреса.

Что я делаю неверно? Может, технология вообще другая должна быть? Или нельзя столько раз JOIN делать с одной и той же таблицей?
Понятно, что в окончательном варианте даты и "Ветераны труда" предполагается сделать параметрами.

[sag]

первый момент: читай http://www.ibase.ru/devinfo/joins.htm и учебники по sql.
второй момент: в твоем запросе действительно перемножение множеств.
третий момент: имхо, "join одной и той же таблицы" тут нафик не нужен:

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

SELECT TM.ADRESS, 
       sum(
       case
         when n.SRV_NAME = 'Отопление' THEN n.summa
         else 0
       end) Otoplenie,
       sum(
       case
         when n.SRV_NAME = 'Холодная вода' THEN n.summa
         else 0
       end) Holvoda,
       ....
FROM THEMAIN TM
join nachisl n  on (O.mid =  tm.mid) and ((O.D_BEGIN  >= '01.01.2005') and  (O.D_END <= '31.01.2005')) 
group by 1

Специфики не знаю, м.б.потребуется "left join nachisl n".
Схемы твоей не знаю (как организована связь THEMAIN<->N_FAMILY)
этот момент проработай сам. Возможно, будет достаточно просто добавить

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

join N_FAMILY NF on (nf.mid = tm.mid) and nf.kat_name containing 'Ветераны труда' 

возможно сложнее.

[Merlin]

Что я делаю неверно? Может, технология вообще другая должна быть? Или нельзя столько раз JOIN делать с одной и той же таблицей?

Да какая разница - с собой, не с собой. Почитай ещё раз что такое join. То, что ты хочешь - либо представлять в виде мастер-деталь с начислениями, либо доставать их подзапросами, либо процедурой с for select по основной таблице и селектами в теле цикла по детали.

[Александр Коковихин]

Если верно понял, требуется только достать из справочных таблиц данные. Для этого когда то давно на форуме IBASE.RU приводился следующий быстро работающий метод, все связанные записи "достаются" за один проход

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

select m_t.id,

(select dt1.val_data from detal_table_1 dt1 where m_t.id = dt1.id),
(select dt2.val_data from detal_table_2 dt2 where m_t.id = dt2.id),
...
(select dt22.val_data from detal_table_22 dt22 where m_t.id = dt22.id)

from master_table1 m_t

where 
m_t.val_data >= :val_data1 and 
m_t.val_data <= :val_data2  

 

Удачи

[Solo]

Спасибо, сегодня же вечером попробую. А объединение самой с собой я как раз из этой статьи и вынес - "про явные и неявные JOIN". До нее я про это дело и не знал

[Solo]

Хмм... Попробовал. Наиболее подходящим показался вариант Александра Коковихина.
Вышло следующее (упрощенно):

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

SELECT tm.MID, tm.adress, tm.square, tm.osquare,

(select nf1.NAME_F from n_family nf1 where nf1.MID = tm.MID),
(select nf2.DOCUM from n_family nf2 where nf2.MID = tm.MID),
(select nf3.D_BORN from n_family nf3 where nf3.MID = tm.MID),

(select n1.SRV_NAME from nachisl n1 where tm.MID = n1.MID and n1.srv_name containing 'Холодная вода'),
(select n2.SRV_NAME from nachisl n2 where tm.MID = n2.MID and n2.srv_name containing 'Горячая вода')

FROM themain tm
where tm.hoster != 'Нет' /*Кроме квартир без хозяина*/

Так он мне ошибку дает

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

multiply rows in singleton select
,
multiply rows in singleton select

Что на древнем санскрите примерно означает "множественные строки в одинарной директиве "селект" ". Ну и какие тут множественные строки? После чего запрос все же срабатывает. Только пустой.

Все это я делаю в IB Experte.

А смысл базы такой:

TheMain (главная таблица)
MID ADRESS
1 ул. Ленина, дом...
2 ул. Сталина, дом...
3 ул. Брежнева, дом...
... и т.д.

От нее - 2 детали
N_FAMILY (таблица проживающих за период)
ID MID F_NAME KAT_NAME D_BEGIN D_END
1 10 Иванов Без льгот 01.01.2005 31.01.2005
2 10 Петров Ветеран труда 01.01.2005 31.01.2005
3 3 Сидоров Без льгот 01.01.2005 31.01.2005

NACHISL (таблица начислений за период)
ID MID SRV NAME D_BEGIN D_END
1 10 Горячая вода 01.01.2005 31.01.2005
2 10 Холодная вода 01.01.2005 31.01.2005
3 3 Отопление 01.01.2005 31.01.2005
...
Задача
Запрос такого вида (привожу только основные поля )
Перечень ветеранов войны (труда, и т.д.) за январь с услугами

АДРЕС | ИМЯ ЛЬГОТНИКА | ХОЛ. ВОДА | ГОР. ВОДА | МУСОР | ОТОПЛЕНИЕ и т.д...

Понятно, что по адресу может проживать и 2 ветерана труда, и адрес может не всеми услугами пользоваться... Тогда в "холодной воде" у него будет сумма, а в горячей - пустое место. В общем, такой вот запрос...

[SAMZ]

Что на древнем санскрите примерно означает "множественные строки в одинарной директиве "селект" ". Ну и какие тут множественные строки? После чего запрос все же срабатывает. Только пустой.

Это означает, что твои Select в круглых скобках мыдают некоторое множество значений в то время как по логике ожидаеися одно значение. И ничего у тебя не срабатывыает. Пустой результат - это совсем не признак срабатывания.
Я бы на твоем месте не мудрил, а написал бы совершенно прозрачную ХП.

[Александр Коковихин]

Присоединяюсь к товарищу...
Нужна уверенность, что в каждом случае подзапрос выдает по одной строке (используется первичный ключ), должно получиться... Хоть и через ХП
Удачи...