Вопрос по SQL

[Rojohn]

Добрый день!
Подскажите пожалуйста, как в запросе SQL к MSAccess написать запрос, в котором выводится результат только в случае наличия связи в условии? В Oracle такие запросы пишутся примерно так: SELECT tbl1.fld2 FROM tbl1,tbl2 WHERE tbl1.fld1=tbl2.fld2(+)

Спасибо!

[VBTerminator]

Имеется в виду связь один-ко-многим?
Тогда примерно так:

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

SHAPE {select FIELDONE,FIELDTWO from TABLEA} AS ParentCMD APPEND ({select FIELDONETWO,FIELDTWOTWO from TABLEB} AS ChildCMD RELATE FIELDONE TO FIELDONETWO) AS ChildCMD

[alibek]

Мда.

Rojohn, тебе нужен left/right outer join.
Можешь в визуальном конструкторе два раза кликнуть на связи и выбрать опцию "Показывать все записи из таблицы tbl2".

[Rojohn]

Не совсем понял, что реализуется у Терминатора.
Алибек тут немного сложнее вроде как (могу ошибаться):
SELECT tbl1.fld2,tbl3.fld2 FROM tbl1,tbl2, tbl3 WHERE (tbl1.fld1=tbl2.fld1) AND (tbl3.fld1=tbl2.fld1);
Суть в том, что есть данные либо по условию (tbl1.fld1=tbl2.fld1), либо по (tbl3.fld1=tbl2.fld1), либо по обоим.
В случае отсутствия записей по какому-либо условию выводится пустое значение ' '. Никак немогу сообразить, чего не хватает. Наверное недогадайка-послепраздничная...
Если проще - надо показывать ВСЕ результаты по указанным связям, включая пустые (без найденных данных, но соответствующие указанным связям).

Если заполнять грид поячеечно, то это решается в несколько запросов. Но мне надо через ADO в один запрос.

[alibek]

У VBTerminator шейпинг, который тут совсем не в тему и который помоему не поддерживается провайдером Jet.
Rojohn, уточни критерии объединения. Потому что в WHERE у тебя указано AND, а на словах ты говоришь "Либо".
Из того, что ты написал, тебе нужен left-join для связи tbl1-tbl2 и простой join для связи tbl2-tbl3.

[Rojohn]

Рассказываю ещё проще:
Есть 3 таблицы: Предметы, Документы1 на предметы, Документы2 на предметы. Есть 2 связующие таблицы (1 предмет ко многим документам каждого вида). На один предмет могут быть выданы либо один из этих двух документов, либо оба, либо ниодного. Нужно вывести в одну строку сведения о Предмете и соответствующие ему номера документов. Если документов нет - выводить пустые значения номеров.

Не думаю, что тут дело только в Join-ах...

[iGrok]

Рассказываю ещё проще:
Есть 3 таблицы: Предметы, Документы1 на предметы, Документы2 на предметы. Есть 2 связующие таблицы (1 предмет ко многим документам каждого вида). На один предмет могут быть выданы либо один из этих двух документов, либо оба, либо ниодного. Нужно вывести в одну строку сведения о Предмете и соответствующие ему номера документов. Если документов нет - выводить пустые значения номеров.

Не думаю, что тут дело только в Join-ах...

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

SELECT Предметы.ИД, Документы1.ИД, Документы2.ИД
FROM (Предметы П LEFT JOIN Документы1 Д1 ON П.ИД = Д1.ИД_Предмета) LEFT JOIN Документы2 Д2 ON П.ИД = Д2.ИД_Предмета

См. вложение.

[alibek]

Не самый удачный вариант, для случая, когда выдано два документа, будет возвращаться две записи (или четыре).
В случае Access я бы советовал использовать перекрестные запросы, они тут хорошо подойдут.
А вообще тебе нужен довольно сложный джойн между Документы1 и Документы2, а уже с результатами этой выборки надо джойнить Предметы.
Я бы советовал изменить структуру базы данных, она не очень удачная.

[iGrok]

Не самый удачный вариант, для случая, когда выдано два документа, будет возвращаться две записи (или четыре).
В случае Access я бы советовал использовать перекрестные запросы, они тут хорошо подойдут.
А вообще тебе нужен довольно сложный джойн между Документы1 и Документы2, а уже с результатами этой выборки надо джойнить Предметы.
Я бы советовал изменить структуру базы данных, она не очень удачная.

Ну я так понял, что у него свзяи один-к-одному. Т.е. на предмет может быть максимум по одному документу из каждой категории. В этом случае оно будет работать именно так, как надо.

В противном случае - да. Менять структуру БД.

З.Ы. alibek, а каким образом на чистом SQL можно получить одну запись если документов из одной таблицы несколько? Понаделать виртуальных полей?
Если честно, я себе представляю как это сделать запросом только при ограниченном возможном кол-ве документов, и то это будет совершенно безумная штука..

UPD: А. Всё. Вижу. Один-ко-многим.. Да. Тогда мой вариант не особо подходит.

[Rojohn]

Вот и у меня повторяются... в Oracl-е вроде как спасает (+), но в Access аналога не знаю. Указанный выше пример почти правильно работает, только там ещё связующие таблицы есть. Попробую реализовать его на своих примерах и надеюсь на более совершенный вариант (если таковой имеется)...

To Alibek: структуру базы менять уже поздно. Как написать такой сложный Join или перекрестный запрос?

[HandKot]

такой вопрос.
Если 1 предмет ко многим документам каждого вида, то как это должно выглядеть в полученном результате? На каждый документ отдельный столбец или?
Просто смущает еще вот это На один предмет могут быть выданы либо один из этих двух документов, либо оба, либо ниодного
пример
Таблица предметов
П1
П2
Таблица документов типа 1
Д1 П1
Д2 П1
Д3 П2
Д4 П2
Д5 П2
Д6 П2

т.е
П1 - Д1, Д2
П2 - Д3, Д4, Д5, Д6
как это должно выглядеть в запросе?

[Rojohn]

Да нет, зачем ещё усложнать?
Есть таблица Предметов c записями:
ID | Title
----------------------------------------------
1 | Пр1
2 | Пр2
3 | Пр3
4 | Пр4

Есть таблица Документов вида1 с записями:
ID | Title
----------------------------------------------
1 | Док1_1
2 | Док1_2

Есть таблица Документов вида2 с записями:
ID | Title
----------------------------------------------
1 | Док2_1
2 | Док2_2

Есть связующая таблица Предметы-Документы вида1 с записями:
ID | PR_ID | DOC1_ID
----------------------------------------------
1 | 1 | 1
2 | 2 | 2

Есть связующая таблица Предметы-Документы вида2 с записями:
ID | PR_ID | DOC2_ID
----------------------------------------------
1 | 1 | 1
2 | 3 | 2

Результат должен быть в виде:
Предмет | Документ1 | Документ2
----------------------------------------------
Пр1 | Док1_1 | Док2_1
Пр2 | Док1_2 | -
Пр3 | - | Док2_2
Пр4 | - | -

Вот собственно и всё. Если на один предмет есть несколько документов одного вида, то выводится новая строка с повтором предмета.

[HandKot]

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

select
  p.title,
  d1.title,
  d2.title
from
  Predmet p
left join pd1 pd1 on pd1.pr_id = p.id
left join document1 d1 on d1.id = pd1.d1_id
left join pd2 pd2 on pd2.pr_id = p.id
left join document2 d2 on d2.id = pd2.d2_id

где
Predmet - таблица Предметов
pd1 - Предметы-Документы вида1
document1 - Документов вида1
pd2 - Предметы-Документы вида2
document2 - Документов вида2

Если на одно оружие есть несколько документов одного вида, то выводится новая строка с повтором предмета

разумеется, если для одного предмета будет 2 документа "1 вида" и 2 документа "2 вида", то кол-во записей будет равно 2*2 = 4 и т.д

[Rojohn]

Спасибо! Буду проверять. Немного не понял момент join pd2 pd2 - разве так пишется? Или это опечатка?

Появился ещё один каверзный запрос для Accessa. Думаю ничего страшного, если я не буду открывать отдельную тему...
Часть первой цепочки с наворотом:
Есть одна таблица Предметов, связанных с таблицей Документов через таблицу Предмет_Документ как 1 ко многим. Таблица Предметов имеет ряд полей с кодом, связанных со справочными таблицами, откуда достаётся наименование по коду.
Есть ещё таблица с историей изменений предметов, имеющая поля ID, Код_Предмета и Состояние_Предмета.
Надо достать в один запрос такие данные:
Предмет, Свойство1_Предмета, Свойство2_Предмета, Состояние_Предмета(из таблицы История_Предметов, у которого ID максимальный).
Таблицы История_Предметов и Предмет_Документ содержат поля Предмет_ID для связи.

Т.е. есть цикл по таблице Предмет_Документ (достаются все предметы, связанные с документом данного номера). На каждое значение по ID предмета достаются 1) Наименование свойств Предмета (из справочных таблиц) и 2) Состояние предмета из таблицы История_Предметов (с максимальным ID).

Если, например, Предмет=Gun, а документ=PERMISSION, то вариант запроса будет таким:

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

SELECT (type_gun.title & '  ' & code_gun.model_title) AS gun_title, gun.SERIES AS gun_series, gun.NUMB AS gun_numb, gun.MAKE_YEAR AS gun_year, gun.PER_DATE AS gun_date, GSTATUS.TITLE AS gun_status
FROM (permission_gun INNER JOIN (history_gun INNER JOIN GSTATUS ON history_gun.GSTATUS_CODE = GSTATUS.CODE) ON (permission_gun.PPERSON_ID = history_gun.PPERSON_ID) AND (permission_gun.GUN_ID = history_gun.GUN_ID)) INNER JOIN ((gun INNER JOIN code_gun ON gun.CODEGUN_CODE = code_gun.CODE) INNER JOIN type_gun ON code_gun.TYPE_GUN_CODE = type_gun.CODE) ON history_gun.GUN_ID = gun.ID
GROUP BY (type_gun.title & '  ' & code_gun.model_title), gun.SERIES, gun.NUMB, gun.MAKE_YEAR, gun.PER_DATE, GSTATUS.TITLE, permission_gun.PERMIS_NUMB, permission_gun.PERMIS_SERIES, gun.UPDATED, permission_gun.PPERSON_ID
HAVING (((permission_gun.PERMIS_NUMB)=11111) AND ((permission_gun.PERMIS_SERIES)='СЕР') AND ((gun.UPDATED)<>5) AND ((permission_gun.PPERSON_ID)=24))
ORDER BY gun.SERIES, gun.NUMB, Max(history_gun.ID) DESC;

Только он не совсем корректный, т.к. выдаётся несколько значений, а не только максимум истории. Если в начале написать TOP 1, то ответ будет верным для одной записи. Но их может быть несколько! Т.е. несколько единиц Предметов GUN в результатах.

Как бы это правильно сделать в один запрос (если возможно, конечно)

[HandKot]

1)

Немного не понял момент join pd2 pd2 - разве так пишется? Или это опечатка

нет просто для таблицы pd2 указал её алиас, который получился pd2
для понимания можно было написать так

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

join pd2 AS pd2

либо не использовать алиас вообще


2)

из таблицы История_Предметов, у которого ID максимальный

цепляйте не саму таблицу а только выборку для предмета с макимальным ID

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

inner join (select GUN_ID, max(id) AS ID ... from history_gun group by GUN_ID) history_gun on ...

[Rojohn]

из таблицы История_Предметов, у которого ID максимальный

цепляйте не саму таблицу а только выборку для предмета с макимальным ID

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

inner join (select GUN_ID, max(id) AS ID ... from history_gun group by GUN_ID) history_gun on ...

Я и пытался это сделать, но куда это писАть? не очень понимаю...
Как достать значения из внутреннего запроса во внешний?

[HandKot]

Состояние предмета из таблицы История_Предметов (с максимальным ID).

Я и пытался это сделать, но куда это писАть? не очень понимаю...

вообще для этого есть 2 способа:

1) просто в select сделать подзапрос

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

select
  field1,
  field2,
  (select top1 filed from histiry where history.predmet = predmet.predmet order by histiry.id desc)
...
from
  predmet
...

2) используя следующую констукцию

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

select
  predmet,
  field
from
  history h
inner join (
  select
    predmet,
    max(id) max_id
  from
    history
  group by
    predmet) max_hist on max_hist.predmet = history.predmet and max_hist.max_id = history.id

и выше изложенную констукцию джойните куда Вам нужно


ЗЫЖ в Вашем примере очень запутанный запрос
если вы выложите структуру таблиц (желательно и пару строк) и что нужно получить в результате, то будет легче написать запрос

[Rojohn]

Спасибо большое!
Сделал по первому примеру:

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

SELECT (type_gun.title & '  ' & code_gun.model_title) AS gun_title, gun.series AS gun_series, gun.numb AS gun_numb, gun.make_year AS gun_year, gun.PER_DATE AS gun_date,
(SELECT TOP 1 gstatus.title FROM gstatus,history_gun WHERE ((history_gun.gun_id=gun.id) AND (gstatus.code=history_gun.gstatus_code)) ORDER BY history_gun.id DESC) AS gun_status, permission_gun.gun_id
FROM permission_gun INNER JOIN ((gun INNER JOIN code_gun ON gun.CODEGUN_CODE = code_gun.CODE) INNER JOIN type_gun ON code_gun.TYPE_GUN_CODE = type_gun.CODE) ON permission_gun.GUN_ID = gun.ID WHERE (((permission_gun.PERMIS_NUMB)=111111) AND ((permission_gun.PERMIS_SERIES)='ссс') AND ((gun.UPDATED)<>5) AND ((permission_gun.PPERSON_ID)=111))
ORDER BY gun.SERIES, gun.NUMB;

Не знал, что так можно написать в ACCESS. Всего-только не писать во внутреннем SELECT внешнюю таблицу из связи...

[Rojohn]

Не тут-то было! Запрос ацки тормозит при большом количестве записей в таблице history_gun
Правда запросов у меня несколько и они объединены между собой (union), но всё равно не понятно, разве Access не может работать быстро? Вроде задача почти тривиальная - максимальный ID в history_gun. Вот мой запрос:

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

Select (TYPE_GUN.TITLE & '  ' & CODE_GUN.MODEL_TITLE) As CODE, GUN.SERIES, GUN.NUMB, GUN.MAKE_YEAR, GUN.PER_DATE, LICENCE_GUN.LIC_SERIES, LICENCE_GUN.LIC_NUMB, PERMISSION_GUN.PERMIS_SERIES, PERMISSION_GUN.PERMIS_NUMB, GUN.ID FROM type_gun, code_gun, gun, licence_gun, permission_gun, history_gun
WHERE ((code_gun.code = gun.codegun_code) AND (gun.id = licence_gun.gun_id) AND (gun.id = permission_gun.gun_id) AND (type_gun.code = code_gun.type_gun_code) AND ((gun.lr)='ЛР') AND ((permission_gun.cur_permis)='Да') AND ((licence_gun.cur_lic)='Да') AND ((gun.pperson_id)=" & IDfl & ") AND (licence_gun.pperson_id=" & IDfl & ") AND (permission_gun.pperson_id=" & IDfl & ") AND ((gun.updated) <> 5) AND (history_gun.id =(
SELECT TOP 1 history_gun.id
FROM history_gun
WHERE ((history_gun.gun_id = gun.id) AND (history_gun.pperson_id=" & IDfl & ") AND (history_gun.pperson_id = gun.pperson_id) AND ((history_gun.gstatus_code=6) OR (history_gun.gstatus_code=9))) ORDER BY history_gun.id DESC)))
Union
SELECT (TYPE_GUN.TITLE & '  ' & CODE_GUN.MODEL_TITLE) AS CODE, GUN.SERIES, GUN.NUMB, GUN.MAKE_YEAR, GUN.PER_DATE, LICENCE_GUN.LIC_SERIES, LICENCE_GUN.LIC_NUMB, '' AS ps, '' AS pn, GUN.ID FROM history_gun,TYPE_GUN, CODE_GUN, GUN,LICENCE_GUN WHERE ((CODE_GUN.CODE = GUN.CODEGUN_CODE) AND (TYPE_GUN.CODE = CODE_GUN.TYPE_GUN_CODE)  AND  (GUN.ID = LICENCE_GUN.GUN_ID)  AND ((GUN.LR)='Л') AND ((LICENCE_GUN.CUR_LIC)='Да') AND ((GUN.PPERSON_ID)=" & IDfl & ") AND ((LICENCE_GUN.PPERSON_ID)=" & IDfl & ") AND ((GUN.UPDATED)<>5)) AND (history_gun.id =(
SELECT TOP 1 history_gun.id
FROM history_gun
WHERE ((history_gun.GUN_ID = GUN.ID) AND (history_gun.pperson_id=" & IDfl & ") AND (history_gun.PPERSON_ID = GUN.PPERSON_ID) AND ((history_gun.gstatus_code=6) OR (history_gun.gstatus_code=9))) ORDER BY history_gun.id DESC))
Union
SELECT (type_gun.title & '  ' & code_gun.model_title) AS code, gun.series, gun.numb, gun.make_year, gun.per_date, '' As ls,'' As ln,permission_gun.permis_series, permission_gun.permis_numb, gun.id
FROM history_gun, type_gun, code_gun, gun, permission_gun WHERE ((code_gun.code = gun.codegun_code) AND (type_gun.code = code_gun.type_gun_code) AND (gun.id = permission_gun.gun_id) AND ((gun.lr)='Р') AND ((permission_gun.cur_permis)='Да') AND ((gun.pperson_id)=" & IDfl & ") AND ((permission_gun.pperson_id)=" & IDfl & ") AND ((gun.updated)<>5)) AND (history_gun.id =(
SELECT TOP 1 history_gun.id
FROM history_gun
WHERE ((history_gun.gun_id = gun.id) AND (history_gun.pperson_id=" & IDfl & ") AND (history_gun.PPERSON_ID = GUN.PPERSON_ID) AND ((history_gun.gstatus_code=6) OR (history_gun.gstatus_code=9))) ORDER BY history_gun.id DESC))
Union
SELECT (type_gun.title & '  ' & code_gun.model_title) AS code, gun.series, gun.numb, gun.make_year, gun.per_date, '' As ls, '' As ln, '' AS ps, '' AS pn, gun.id
FROM history_gun, type_gun, code_gun, gun
WHERE ((code_gun.code = gun.codegun_code) AND (type_gun.code = code_gun.type_gun_code) AND ((GUN.LR) Is Null) AND ((gun.pperson_id)=" & IDfl & ") AND ((gun.updated)<>5)) AND (history_gun.id =(
SELECT TOP 1 history_gun.id
FROM history_gun
WHERE ((history_gun.gun_id = gun.id) AND (history_gun.pperson_id=" & IDfl & ") AND (history_gun.PPERSON_ID = GUN.PPERSON_ID) AND ((history_gun.gstatus_code=6) OR (history_gun.gstatus_code=9))) ORDER BY history_gun.id DESC))
Order By GUN.SERIES,GUN.NUMB;

Может кто-нибудь поможет модернизировать его так, чтобы он работал быстрее
Огромное спасибо заранее

[iGrok]

Ой #@$*№!!! (это не мат - это "нет слов".)
Это что за каша?

Ради того, чтобы начать хоть с чего-то, начнём с этого:

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

  SELECT TOP 1 history_gun.id
  FROM history_gun
  WHERE ( (history_gun.gun_id = gun.id) AND
       (history_gun.pperson_id=" & IDfl & ") AND
     (history_gun.pperson_id = gun.pperson_id) AND
     ((history_gun.gstatus_code=6) OR (history_gun.gstatus_code=9))
        )
  ORDER BY history_gun.id DESC


Чего ты этим хочешь получить?
Особенно интересны моменты: history_gun.gun_id = gun.id и (history_gun.pperson_id=" & IDfl & ") AND (history_gun.pperson_id = gun.pperson_id).

[Rojohn]

Этот запрос был написан, исходя из предыдущего обсуждения.
Думаю, вопрос снимается, т.к. оказалось, что вложенный SELECT в данном случае не обязателен.

Так для интереса, остался один вопрос: неужели вложенные SELECTы так сильно тормозят запросы

[iGrok]

Так для интереса, остался один вопрос: неужели вложенные SELECTы так сильно тормозят запросы

Обычно нет. Нормально построенные вложенные селекты ничего не тормозят.
Но ты уверен, что ты понимаешь, ЧТО ИМЕННО делал этот вложенный селект?

[Rojohn]

Да, вероятно строчка:

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

(history_gun.pperson_id = gun.pperson_id) AND

лишняя!

А ищу я там максимальное значение ID среди записей, связаных с вышестоящим запросом и ограниченных соответствующими условиями...