JOIN (синтаксис)

← →
supersan (2003-05-05 12:53) [0]

Доброе время суток, господа. Если не затруднит, помогите найти ошибку в синтаксисе. Есть SQL-запрос:
select c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c, spBank b, spDol d, spBox1 b1, spBox2 b2, spVPens vp, spPPV ppv, tbWB wb, tbPens p, tbPV pv where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.ID_Bank=b.ID) and /*-- здесь --*/ (b.Name like "%%") and (p.ID_Card=c.ID) and (p.ID_VPens=vp.ID) and /*-- здесь --*/ (vp.Name like "%%") and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (pv.ID_Card=c.id) and (pv.ID_PPV=ppv.ID) and /*-- здесь --*/ (ppv.Name like "%%") and (wb.ID_Card=c.ID) and (wb.ID_Dol=d.ID) and /*-- здесь --*/ (d.Name like "%%") and (wb.ID_Box1=b1.ID) and /*-- здесь --*/ (b1.Name like "%%") and (wb.ID_Box2=b2.ID) and /*-- здесь --*/ (b2.Name like "%%")
Он выдает слишком мало записей, что обусловлено отсутствием записей в таблицах tbWB, tbPens, tbPV, связанных с tbCard, и, как следствие, связанных с spDol, spBox1, spBox2, spVPens, spPPV.
В связи с этим хотелось бы вместо равенства в строчках, помеченных /*-- здесь --*/ поставить что-то типа (+) (что есть в Oracle). В IB придется использовать LEFT|RIGHT JOIN. К сожалению, что-то запутался в синтаксисе. Не мог бы кто-нибудь написать, как правильно синтаксически енто дело оформить?
Заранее благодарен.
PS На всякий случай - структура БД (точнее, ее части)
tbCard *--------- spBank / | \ / | \ / | \ / | \ */ *| *\ tbWB tbPens tbPV */*|*\ *| *\ / | \ | \ / | \ spVPens spPPV / | \ spDol spBox1 spBox2
К сожалению, красивее не нарисуешь :-(. Где *, там "многие".
И еще. Просьба не отсылать к хелпу, т.к. счас вот оттуда, что-то мне это дело не помогло...

← →
NickBat (2003-05-05 12:58) [1]

select c.ID, c.Num_D, c.Fam, c.Ima, c.Otc
from tbCard c
join spbank b on c.ID_BANK=b.ID
where (c.Fam like "%%") and
(c.Ima like "%%") and
(c.Otc like "%%")

При необходимости добавляешь еще строки с join

← →
supersan (2003-05-05 13:11) [2]

> NickBat © (05.05.03 12:58)

Это, конечно, хорошо, и даже работает. С хелпом я так добился. Но Вы привели достаточно легкий пример. А как насчет JOIN таблицы tbWB с таблицами sbDol, spBox1 и spBox2? Если можно, то заострите внимание именно на этом обстоятельстве. Заранее благодарен за работающий код.

← →
NickBat (2003-05-05 13:30) [3]

select c.ID, c.Num_D, c.Fam, c.Ima, c.Otc
from tbCard c
join spbank b on c.ID_BANK=b.ID
join tbPens p on p.ID_Card=c.ID
join spVPens vp on p.ID_VPens=vp.ID
where (c.Fam like "%%") and
(c.Ima like "%%") and
(c.Otc like "%%") and
(b.Name like "%%")

Ну и так далее, уж больно длинно получается :))
PS: Каждый следующий join можно привязать к любому полю из уже связанных таблиц.

← →
Desdechado (2003-05-05 13:31) [4]

может, проще ХП написать?
для запросов такого вида я так и поступаю - по очереди выбираешь, коллекционируешь строку и SUSPEND ее

← →
Johnmen (2003-05-05 13:56) [5]

... FROM T1 ...
LEFT JOIN T2 ...
LEFT JOIN T3 ...

Каждый последующий л.джоин соединяет так, что отсутствующие соответствующие записи к текущему выполненному соединению будут включены с пустыми значениями полей...

← →
supersan (2003-05-05 14:01) [6]

Спасибо. Окончательный вариант выглядит так
select c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c JOIN spBank b ON c.ID_Bank=b.ID, tbPV pv JOIN spPPV ppv ON pv.ID_PPV=ppv.ID, tbPens p JOIN spVPens vp ON p.ID_VPens=vp.ID, tbWB wb JOIN spDol d ON wb.ID_Dol=d.ID JOIN spBox1 b1 ON wb.ID_Box1=b1.ID JOIN spBox2 b2 ON wb.ID_Box2=b2.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (b.Name like "%%") and (p.ID_Card=c.ID) and /*-- здесь --*/ (vp.Name like "%%") and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (pv.ID_Card=c.id) and /*-- здесь --*/ (ppv.Name like "%%") and (wb.ID_Card=c.ID) and /*-- здесь --*/ (d.Name like "%%") and (b1.Name like "%%") and (b2.Name like "%%")
Результат этого запроса:
ID NUM_D FAM IMA OTC ==== ===== =============== ================== ================== 2 1 Иванов Иван Иванович 4 2 Петров Петр Петрович

А это не все. Потому что:
select id, num_d, fam, ima, otc from tbCard ID NUM_D FAM IMA OTC ==== ===== =============== ================== ================== 2 1 Иванов Иван Иванович 4 2 Петров Петр Петрович 8 3 Сидоров Сидор Сидорович 11 4 Ямщиков Ямщик Ямщикович
Просто для Сидорова и Ямщикова нет записей в таблицах tbWB, tbPens, tbPV (а хотелось и их тоже получить в выборке). Может, надо еще сJOINить еще в местах, помеченных /*-- здесь --*/? Тогда появляются вопросы:
1. Как насчет синтаксиса опять?
2. Влияет ли порядок таблиц до JOIN и после него на результат?
Насчет > Desdechado © (05.05.03 13:31)
Я просто пытаюсь выполнить запрос на выборку на клиенте, посчитал, что так проще будет добиться результата.

← →
Zacho (2003-05-05 14:40) [7]

> supersan © (05.05.03 14:01)

Не совсем понял как именно связаны таблицы и что именно надо получить, но вот краткий пример. Дальше думаю сам сообразишь.
select c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN tbWb ON wb.ID_Card=c.ID JOIN spDol d ON wb.ID_Dol=d.ID JOIN spBox1 b1 ON wb.ID_Box1=b1.ID JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT JOIN tbPens p ON p.ID_Card=c.ID ... WHERE ...
Соединения с остальными таблицами ,думаю, сам допишешь. "что-то" like "чего-то" добавить или в WHERE или в условия соответствующих JOIN"ов.

← →
Zacho (2003-05-05 14:50) [8]

> Zacho © (05.05.03 14:40)

Немного описался, следует читать: LEFT JOIN tbWb wb ON wb.ID_Card=c.ID

← →
Desdechado (2003-05-05 14:56) [9]

> Просто для Сидорова и Ямщикова нет записей в таблицах tbWB,
> tbPens, tbPV (а хотелось и их тоже получить в выборке)
тогда LEFT OUTER JOIN

← →
Johnmen (2003-05-05 15:07) [10]

>Desdechado © (05.05.03 14:56)

LEFT JOIN <=> LEFT OUTER JOIN = LEFT [OUTER] JOIN

← →
supersan (2003-05-22 14:39) [11]

Господа, помогите, плиз. Опять вернулся к этому вопросу, вроде бы уже все, ан нет, оказывается, не все оказалось. Попробовал сделать соединение таким образом:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT OUTER JOIN spBank b ON c.ID_Bank=b.ID, tbPV pv LEFT OUTER JOIN spPPV ppv ON pv.ID_PPV=ppv.ID LEFT OUTER JOIN tbCard ON pv.ID_Card=tbCard.ID, tbPens p LEFT OUTER JOIN spVPens vp ON p.ID_VPens=vp.ID LEFT OUTER JOIN tbCard ON p.ID_Card=tbCard.ID, tbWB wb LEFT OUTER JOIN spDol d ON wb.ID_Dol=d.ID LEFT OUTER JOIN spBox1 b1 ON wb.ID_Box1=b1.ID LEFT OUTER JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT OUTER JOIN tbCard ON wb.ID_Card=tbCard.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0") and (vp.Name like "%%") and (vp.Deleted="0") and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (p.Deleted="0") and (ppv.Name like "%%") and (ppv.Deleted="0") and (pv.Deleted="0") and (wb.Deleted="0") and (d.Name like "%%") and (d.Deleted="0") and (b1.Name like "%%") and (b1.Deleted="0") and (b2.Name like "%%") and (b2.Deleted="0")
Результат: нет записей
Попробовал таким:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_Bank=b.ID, tbPV pv LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID RIGHT JOIN tbCard ON pv.ID_Card=tbCard.ID, tbPens p LEFT JOIN spVPens vp ON p.ID_VPens=vp.ID RIGHT JOIN tbCard ON p.ID_Card=tbCard.ID, tbWB wb LEFT JOIN spDol d ON wb.ID_Dol=d.ID LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID RIGHT JOIN tbCard ON wb.ID_Card=tbCard.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0") and (vp.Name like "%%") and (vp.Deleted="0") and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (p.Deleted="0") and (ppv.Name like "%%") and (ppv.Deleted="0") and (pv.Deleted="0") and (wb.Deleted="0") and (d.Name like "%%") and (d.Deleted="0") and (b1.Name like "%%") and (b1.Deleted="0") and (b2.Name like "%%") and (b2.Deleted="0")
Результат: тот же.
Попробовал еще поперебирать Left и Right Join, но не смог выполнить полный перебор (по причине его обширности :-().
Тогда как
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c
выдает результат:
ID NUM_D FAM IMA OTC
==== ===== =========== =========== ==================
2 1 1 1 1
8 2 2 2 2
9 3 3 3 3
10 4 4 4 4
11 5 5 5 5

Я же хочу выстроить запрос с JOIN"ами (или без оных), чтоб выдавал тот же результат (т.е., если юзер не обозначил никаких критериев поиска, то выдавалась бы вся таблица tbCard)
PS Структуру БД см > supersan © (05.05.03 12:53)
PPS В таблицах tbWB, tbPens, tbPV нет никаких записей.

← →
supersan (2003-05-23 07:29) [12]

← →
supersan (2003-05-23 12:51) [13]

Попробовал совет > Zacho © (05.05.03 14:40)
Результат:
select c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN tbWb wb ON wb.ID_Card=c.ID JOIN spDol d ON wb.ID_Dol=d.ID JOIN spBox1 b1 ON wb.ID_Box1=b1.ID JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT JOIN tbPens p ON p.ID_Card=c.ID

ID NUM_D FAM IMA OTC
== ===== ========= =========== ==============
2 1 1 1 1
PS Для данной записи есть связанные в таблице tbWB (точнее, одна запись). Для других записей из tbCard связанных записей в tbWB нет, они и не выдаются.
Тогда как
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c
выдает результат:
ID NUM_D FAM IMA OTC
== ===== ========= =========== ===============
2 1 1 1 1
8 2 2 2 2
9 3 3 3 3
10 4 4 4 4
11 5 5 5 5

← →
Zacho (2003-05-23 13:23) [14]

> supersan © (23.05.03 12:51)

Ну делай так:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc
from tbCard c
LEFT JOIN tbWb ON wb.ID_Card=c.ID
LEFT JOIN spDol d ON wb.ID_Dol=d.ID
LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID
LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID
LEFT JOIN tbPens p ON p.ID_Card=c.ID
... и т.д. ...
WHERE
...

← →
supersan (2003-05-23 13:53) [15]

> Zacho © (23.05.03 13:23)
Ага, спасибо, так работает. Но! Попробовал по аналогии расширить запрос, т.е.
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN tbWb wb ON wb.ID_Card=c.ID LEFT JOIN spDol d ON wb.ID_Dol=d.ID LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT JOIN tbPens p ON p.ID_Card=c.ID LEFT JOIN tbPV pv ON pv.ID_Card=c.ID LEFT JOIN spBank b ON c.ID_BANK=b.ID LEFT JOIN spVPens vp ON p.ID_VPens=vp.ID LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID where (c.Fam like "%%") and /* здесь */ (c.Ima like "%%") and /* здесь */ (c.Otc like "%%") and /* здесь */ (c.Deleted="0") and (b.Name like "%%") and /* здесь */ (b.Deleted="0") and (vp.Name like "%%") and /* здесь */ (vp.Deleted="0") and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (p.Deleted="0") and (ppv.Name like "%%") and /* здесь */ (ppv.Deleted="0") and (pv.Deleted="0") and (wb.Deleted="0") and (d.Name like "%%") and /* здесь */ (d.Deleted="0") and (b1.Name like "%%") and /* здесь */ (b1.Deleted="0") and (b2.Name like "%%") and /* здесь */ (b2.Deleted="0")
то опять ничего не выдал :-(
PS В строчках, помеченных /* здесь */ формируется таки сам запрос. Как видно, все значения пустые, но все равно ничего нет (выборка пуста)
Если написать тот же запрос без ветки where, то выдает все записи, но это же не есть выход, я правильно полагаю?
Итак, вопрос остался пока открытым: что же все-таки сделать?
PPS Предложения изменить ДНК не принимаются :-)

← →
Zacho (2003-05-23 14:02) [16]

> supersan © (23.05.03 13:53)

Значит, что-то неправильно в условиях в WHERE.
Попробуй сначало выкинуть все, кроме одного, потом добавляй по одному - в конце концов найдешь ошибку

← →
supersan (2003-05-23 14:35) [17]

Итак, полный запрос
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID LEFT JOIN tbPV pv ON pv.ID_Card=c.ID LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID LEFT JOIN tbWb wb ON wb.ID_Card=c.ID LEFT JOIN spDol d ON wb.ID_Dol=d.ID LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT JOIN tbPens p ON p.ID_Card=c.ID LEFT JOIN spVPens vp ON p.ID_VPens=vp.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0") and (ppv.Name like "%%") and (ppv.Deleted="0") and (pv.Deleted="0") and (wb.Deleted="0") and (d.Name like "%%") and (d.Deleted="0") and (b1.Name like "%%") and (b1.Deleted="0") and (b2.Name like "%%") and (b2.Deleted="0") and (vp.Name like "%%") and (vp.Deleted="0") and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (p.Deleted="0")
Результат: 0 записей. Неправильно...
Начинаю обрезать:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID LEFT JOIN tbPV pv ON pv.ID_Card=c.ID LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0") and (ppv.Name like "%%") and (ppv.Deleted="0") and (pv.Deleted="0")
Результат: 0 записей. Неправильно...
Наконец:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0")
Результат: найдены все записи (5 штук). Правильно!
Вывод: дело не в where, а в неправильном join. Не прав?

← →
Zacho (2003-05-23 14:41) [18]

Ну так вместе с JOIN ты обрезаешь и WHERE. Попробуй оставить все джойны и обрезать WHERE. Кстати ты уже можешь быть уверен, что
(c.Fam like "%%") and
(c.Ima like "%%") and
(c.Otc like "%%") and
(c.Deleted="0") and
(b.Name like "%%") and
(b.Deleted="0")
праильные

← →
supersan (2003-05-23 15:05) [19]

ОК, такой запрос:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID LEFT JOIN tbPV pv ON pv.ID_Card=c.ID LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID LEFT JOIN tbWb wb ON wb.ID_Card=c.ID LEFT JOIN spDol d ON wb.ID_Dol=d.ID LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT JOIN tbPens p ON p.ID_Card=c.ID LEFT JOIN spVPens vp ON p.ID_VPens=vp.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0")
Результат: 5 записей
Как только добавляю еще одно (одно!) условие AND, то получаю:
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID LEFT JOIN tbPV pv ON pv.ID_Card=c.ID LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID LEFT JOIN tbWb wb ON wb.ID_Card=c.ID LEFT JOIN spDol d ON wb.ID_Dol=d.ID LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID LEFT JOIN tbPens p ON p.ID_Card=c.ID LEFT JOIN spVPens vp ON p.ID_VPens=vp.ID where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0") and (b.Name like "%%") and (b.Deleted="0") and (ppv.Name like "%%")
Результат: 0 записей.
Вывод: Согласен, что дело в where. Но почему??? И, главное, как это исправить??? Или все-таки не в where, а в join?

← →
supersan (2003-05-23 15:32) [20]

Ладно, пора домой. Если будут какие-нибудь идеи - пишите, не стесняйтесь. Доживем до понедельника ((с) не знаю, кто).

← →
Zacho (2003-05-23 17:03) [21]

Честно говоря, ломы мне так разбираться. Я же не знаю, какие данные у тебя в таблицах. Если есть желание - пришли мне на мыло заархивированный gbk, на досуге покопаюсь. Только именно gbk и еще чем-нибудь (лучше winrar3.x) заархивированный. Надеюсь, он не слишком большой :-)
P.S. Лучше шли на zacho@inbox.ru

← →
supersan (2003-05-26 07:40) [22]

2 > Zacho © (23.05.03 17:03)
Ок, выслал на zacho@inbox.ru sql-файлы для создания БД, заархивировано winrar 3.11. Там есть подробное (надеюсь) описание требуемого результата.

← →
Zacho (2003-05-26 08:53) [23]

Перенеси все условия, не относящиеся к таблице tbCard из WHERE в JOIN с сллтветствующими таблицами, т.е.

select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID and (b.Name like "%%") and(b.Deleted="0") where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0")
и т.д.
Дело в том, что при соединении OUTER JOIN, если условие соединения не выполняется, то в соответствующих полях будет NULL, и, естественно, условия типа (b.Name like "%%") and(b.Deleted="0") не выполнятся.

← →
supersan (2003-05-26 13:04) [24]

БОЛЬШОЕ ЧЕЛОВЕЧЕСКОЕ СПАСИБО!!!
Окончательный вариант запроса получился следующий (так, вдруг кто-нибудь заинтересуется)
select distinct c.ID, c.Num_D, c.Fam, c.Ima, c.Otc from tbCard c LEFT JOIN spBank b ON c.ID_BANK=b.ID and (b.Name like "%%") and (b.Deleted="0") LEFT JOIN tbPV pv ON pv.ID_Card=c.ID and (pv.Deleted="0") LEFT JOIN spPPV ppv ON pv.ID_PPV=ppv.ID and (ppv.Name like "%%") and (ppv.Deleted="0") LEFT JOIN tbWb wb ON wb.ID_Card=c.ID and (wb.Deleted="0") LEFT JOIN spDol d ON wb.ID_Dol=d.ID and (d.Name like "%%") and (d.Deleted="0") LEFT JOIN spBox1 b1 ON wb.ID_Box1=b1.ID and (b1.Name like "%%") and (b1.Deleted="0") LEFT JOIN spBox2 b2 ON wb.ID_Box2=b2.ID and (b2.Name like "%%") and (b2.Deleted="0") LEFT JOIN tbPens p ON p.ID_Card=c.ID and (p.Date_NV>="01.01.1900") and (p.Date_NV<="31.12.2099") and (p.Deleted="0") LEFT JOIN spVPens vp ON p.ID_VPens=vp.ID and (vp.Name like "%%") and (vp.Deleted="0") where (c.Fam like "%%") and (c.Ima like "%%") and (c.Otc like "%%") and (c.Deleted="0")
И все работает!!!! УРА!!!

JOIN (синтаксис) Найти похожие ветки