Цепочка запросов, выбирающих таблицы

[Arinyshka]

Есть несколько таблиц, внешних ключей меж ними не определено:
plattreb - base - t_goods - t_subgroup - t_group
Я уже умею выбирать по сведению из plattreb данные из t_group, если везде выбирается одно поле (Посмотреть в plattreb строчку для выборки в base, по результату выбрать строчку из t_goods и тд).
Но. Таблица t_goods содержит список всевозможных товаров, объединенных в подгруппы  t_subgroup, объединенных в свою очередь в группы t_group.
Соответственно моя выстраданная хранимая

Код:

CREATE PROCEDURE t_sel_PurpPay
--@tempIdTreb int
@tempIdPostavka int
AS
declare

@tempIdGoods int,
@tempid_subGroup int,
@tempIdGroup int
BEGIN
/*
set @tempIdPostavka = (select
t.id_postavka
From Treb t
where t.id_treb = @tempIdTreb) 
*/

set @tempIdGoods = (select
b.id_goods
From Base b
where b.id_postavka = @tempIdPostavka)

set @tempid_subGroup = (select
g.id_SubGroup
from T_Goods g
where g.id_Goods = @tempIdGoods)

set @tempIdGroup = (select
sg.id_Group
from T_SubGroup sg
where sg.id_SubGroup = @tempid_subGroup)

select PurpPay
from T_Group tg
where @tempIdGroup = tg.id_Group 
/* по номеру Требования находит номер назначения требования*/
END

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

[Finch]

Можно ли узнать, какая именно база данных? (ֵMySQL, MSSQL server, Oracle)

[Dimka]

Finch, Судя по синтаксису, может быть SQL Server.

Arinyshka, постановка задачи неверная. Что значит нет внешних ключей? Если ключи не объявлены в базе данных, это не значит, что логически нет никаких связей между таблицами. Если бы не было связей, ты бы вообще ничего не смогла выбрать. Раз ты делаешь выбор, такие связи есть.

А теперь опиши структуры таблиц и правила, по которым ты пытаешься связывать таблицы.

Затем опиши, что хочешь получить. Если у тебя входное значеие id_treb, а выходное PurpPay, то, судя по тексту твоей хранимой процедуры, это вообще одним запросом делается:

Код: (Text)

SELECT T_Group.PurpPay
FROM
    Treb
        INNER JOIN Base ON Treb.id_postavka = Base.id_postavka
        INNER JOIN T_Goods ON Base.id_goods = T_Goods.id_goods
        INNER JOIN T_SubGroup ON T_Goods.id_SubGroup = T_SubGroup.id_SubGroup
        INNER JOIN T_Group ON T_SubGroup.id_Group = T_Group.id_Group
WHERE Treb.id_treb = @tempIdTreb

[Arinyshka]

УУпс..., dimka, работает Будьте добры, поясните, как работает INNER JOIN... мне http://technet.microsoft.com/ru-ru/library/aa213234(en-us,SQL.80).aspx не очень-то помог, я все равно не все понимаю. Запрос нужно усложнить на еще одну ступеньку, я хочу понять и сделать это сама...

Даже уже изменила  Но боюсь, конструкцию с малость другими требованиями не построю...  Ладно, бум читать 

[Finch]

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

Код: (SQL)

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1ID

эквивалентно запросу

Код: (SQL)

SELECT * FROM  table1, table2 WHERE  table1.id = table2.table1ID

Есть также LEFT JOIN, RIGHT JOIN, CROSS JOIN

[Arinyshka]

угу... а чтобы вернуло по одному значению, distinct,  да? кажется, понятненько... Пасиб

[Finch]

Нет, могут быть возрашены множество значений, все зависит как связаны таблици. Просто с INNER JOIN запрос считается стандартным, который был изначально в ּSQL. LEFT JOIN и RIGHT JOIN сложновато с эмулировать например в другом виде. CROSS JOIN эквивалентен

Код: (SQL)

SELECT * FROM  table1, table2

[Finch]

LEFT JOIN - объединяет две таблици, если есть записи в первой таблице, которые не соответсвуют записям из второй таблици, то они тоже включаются, только поля со второй таблици помечаются как пустые (NULL).
ּRIGHT JOIN - эквивалентен LEFT JOIN только для второй таблици.

Простой пример использования. Есть две таблици. Первая содержит данные по персоналу, вторая содержит контактные данные, привязка идет по id людей. Нужно выбрать людей, для которых не занесены контактные данные. Тута поможет LEFT JOIN. Другие способы намного сложнее.

[Arinyshka]

Пасиб  Как-то у вас намного понятнее получается, чем в учебниках )

[Dimka]

LEFT JOIN, RIGHT JOIN, CROSS JOIN

Ещё FULL [OUTER] JOIN забыл

[Arinyshka]

Продолжаем тему, если позволите Теперь хранимая возвращает -1, если выбрано записей больше чем одна и id_PurpPay , если найдена ровно 1 запись. Можно ли по найденному T_Group.PurpPay вернуть не его, а МЕСТО, на котором стоит запись с этим id в другой таблице? То есть я нахожу, например, что моему запросу соответствует id_PurpPay = 41. И в качестве результата возвращаю 1, потому что в таблице PurposeOfPayment платеж с уник. номером (первичным ключем) id_PurposePay = 41 стоит на 1-ом месте при упорядочении по  id_PurposePay. Честно говоря, не представляю, как выбрать именно позицию в списке... Понимаю, как я могу выкрутиться на уровне дельфы, но хочется сделать это в хранимой, если возможно

[Sla]

Arinyshka, не совсем понятно что ты хочешь получить
давай по шагам

[Arinyshka]

ооой Забыла вкинуть хранимую - выходные прошли не зря  

Код:

CREATE PROCEDURE t_sel_PurpPay
@tempIdTreb int
AS
BEGIN
  SELECT DISTINCT
     CASE 
        WHEN COUNT(*) OVER(partition BY v01.id_postavka) > 1 then 
         -1 
        ELSE 
          v01.PurpPay 
     END AS PurpPay
from     
(
SELECT DISTINCT
  Treb.id_postavka, T_Group.PurpPay 
FROM
    PlatTreb
        INNER JOIN Treb ON PlatTreb.id_treb = Treb.id_treb
        INNER JOIN Base ON Treb.id_postavka = Base.id_postavka
        INNER JOIN T_Goods ON Base.id_goods = T_Goods.id_goods
        INNER JOIN T_SubGroup ON T_Goods.id_SubGroup = T_SubGroup.id_SubGroup
        INNER JOIN T_Group ON T_SubGroup.id_Group = T_Group.id_Group
WHERE PlatTreb.id_PlTreb = @tempIdTreb) v01
/* по номеру Требования находит номер назначения требования*/
END

По цепочке таблиц мне в результате  выборки

Код:

SELECT DISTINCT
  Treb.id_postavka, T_Group.PurpPay 
FROM
    PlatTreb
        INNER JOIN Treb ON PlatTreb.id_treb = Treb.id_treb
        INNER JOIN Base ON Treb.id_postavka = Base.id_postavka
        INNER JOIN T_Goods ON Base.id_goods = T_Goods.id_goods
        INNER JOIN T_SubGroup ON T_Goods.id_SubGroup = T_SubGroup.id_SubGroup
        INNER JOIN T_Group ON T_SubGroup.id_Group = T_Group.id_Group
WHERE PlatTreb.id_PlTreb = @tempIdTreb) v01

вернется либо одно, либо несколько значений. В случае, если несколько, на выходе хранимая выдаст -1. Если одно - то его, то есть поле T_Group.PurpPay. Это работает.
Теперь я хочу посмотреть, на каком месте стоит поле id_PurposePay со значением = T_Group.PurpPay в таблице PurposeOfPayment, если запись одна. И в качестве результата процедуры получить именно порядковый номер записи в таблице PurposeOfPayment, упорядоченной по возрастанию id_PurposePay. Или все ту же (-1), если записей оказалось несколько.

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

[Sla]

ух круто

Код:

CREATE PROCEDURE t_sel_PurpPay
@tempIdTreb int
AS
BEGIN
  SELECT DISTINCT
     CASE 
        WHEN COUNT(*) OVER(partition BY v01.id_postavka) > 1 then 
         -1 
        ELSE 
          v01.PurpPay 
     END AS PurpPay
from     
(
SELECT DISTINCT
  Treb.id_postavka, T_Group.PurpPay 
FROM
    PlatTreb
        INNER JOIN Treb ON PlatTreb.id_treb = Treb.id_treb
        INNER JOIN Base ON Treb.id_postavka = Base.id_postavka
        INNER JOIN T_Goods ON Base.id_goods = T_Goods.id_goods
        INNER JOIN T_SubGroup ON T_Goods.id_SubGroup = T_SubGroup.id_SubGroup
        INNER JOIN T_Group ON T_SubGroup.id_Group = T_Group.id_Group
WHERE PlatTreb.id_PlTreb = @tempIdTreb) v01
/* по номеру Требования находит номер назначения требования*/
END

используя DISTINCT, ты уже обрезала повторяющиеся записи.
Так о каком порядковом номере может идти речь?

[Dimka]

Arinyshka, в реляционной алгебре нет понятия порядкового номера. Запрос оперирует множеством (неупорядоченным). Если ты задаёшь порядок, то можно получать номер, только простых и очевидных средств для этого нет - в подавляющем большинстве случаев номера просто не нужны, и поэтому решение сильно задвисит о СУБД, которой ты пользуешься.

Лучше расскажи, зачем тебе этот номер. Если не для "красоты", то на 80% уверен, что он тебе на самом деле не нужен.

[Arinyshka]

Arinyshka, в реляционной алгебре нет понятия порядкового номера. Запрос оперирует множеством (неупорядоченным). Если ты задаёшь порядок, то можно получать номер, только простых и очевидных средств для этого нет - в подавляющем большинстве случаев номера просто не нужны, и поэтому решение сильно задвисит о СУБД, которой ты пользуешься.

Лучше расскажи, зачем тебе этот номер. Если не для "красоты", то на 80% уверен, что он тебе на самом деле не нужен.

Эээх... от лени . Я в порядке ORDER BY PurposePay выбираю (1) поля для заполнения комбокса, из другой таблицы в том же порядке - (2) рисунки... теперь понадобился этот запрос (3). Если бы я получала номер в упорядоченном списке, то я бы его сразу и использовала при выборе, например, itemIndex. Если просто получать id, как сейчас - то мне нужно в (1) менять структуру данных - считывать поля в массив записей, а для получения номера итема в комбобоксе организовывать по нему поиск... Массив используется в нескольких юнитах, менять много...  хотелось выкрутиться проще Оказалось, что не проще...

[Arinyshka]

ух круто
используя DISTINCT, ты уже обрезала повторяющиеся записи.
Так о каком порядковом номере может идти речь?

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

[Sla]

Arinyshka, знаешь в чем твоя проблема?
Ты рассказываешь что нужно сделать со ссылкой на уже существующий код.
Если бы ты сразу определилась с ТЗ, то, возможно, у тебя и не было бы таких вопросов.

Кроме того, в запрос в хранимой процедуре можно, наверное, и упростить. например не использовать вложенный запрос.

Ты что-нибудь о планах запросов слышала?

[Arinyshka]

Моя проблема сразу в нескольких вещах  :'(
1) Полное отсутствие опыта и незнание не то что t-sql, но и моментов, которые мне нужно выспросить - поэтому пытаю Вас, а не гуглу...
2) Созданная в проекте БД, в которой нельзя менять структуру таблиц - я должна действовать в рамках того, что есть
3) возникновение ТЗ "по ходу событий". То есть в какой-то момент были созданы хранимые и их обработчики, мне нужно дополнять код вдруг возникшими пожеланиями в рамках существующего. Я и так-то местами плохо понимаю, что делаю, а когда шаг вправо, шаг влево - попытка к бегству... 
на самом деле, спасибо за терпение Я понимаю, что на меня его ох как много нужно 
Я все-таки закрыла проблему обработчиком в дельфе. Буду знать, что порядковый номер записи в запросе - дело нетривиальное, постараюсь избегать в дальнейшем.  Сорри за мою глупость 
P.S. Вот же мужской форум - ни одного рыдающего смайлика на основной панели!

[Sla]

как по мне, так такой запрос легче читается

Код:

SELECT DISTINCT
     CASE 
        WHEN COUNT(*) OVER(partition BY Treb.id_postavka) > 1 then 
         -1 
        ELSE 
          v01.PurpPay 
     END AS PurpPay
from     PlatTerb, Treb, Base, T_Goods, T_SubGroup, T_Group
where 
    PlatTreb.id_PlTreb   = @tempIdTreb
and PlatTreb.id_treb     = Treb.id_treb
and Treb.id_postavka     = Base.id_postavka
and Base.id_goods        = T_Goods.id_goods
and T_Goods.id_SubGroup  = T_SubGroup.id_SubGroup
and T_SubGroup.id_Group  = T_Group.id_Group

и...
как я вижу, DISTINCT здесь совсем не нужен, потому что ты уже все обрезала COUNT

НО! я не уверен что он работает

[Arinyshka]

как по мне, так такой запрос легче читается

Код:

SELECT DISTINCT
     CASE 
        WHEN COUNT(*) OVER(partition BY Treb.id_postavka) > 1 then 
         -1 
        ELSE 
          v01.PurpPay 
     END AS PurpPay
from     PlatTerb, Treb, Base, T_Goods, T_SubGroup, T_Group
where 
    PlatTreb.id_PlTreb   = @tempIdTreb
and PlatTreb.id_treb     = Treb.id_treb
and Treb.id_postavka     = Base.id_postavka
and Base.id_goods        = T_Goods.id_goods
and T_Goods.id_SubGroup  = T_SubGroup.id_SubGroup
and T_SubGroup.id_Group  = T_Group.id_Group

и...
как я вижу, DISTINCT здесь совсем не нужен, потому что ты уже все обрезала COUNT

НО! я не уверен что он работает

Попытаюсь оправдаться:
DISTINCT мне потребовался потому, что если мне вернутся 100 записей с одинаковым полем - меня это ни разу не смущает. Мне нужно, чтобы не было записей с разным полем. DISTINCT, согласно книжкам, обрезает именно повторяющиеся записи. Таким образом, им я игнорю повторы, а COUNT мне скажет, были ли разные поля в результате.
Я не спорю, я уточняю - если неправа, то подскажите почему 

И начинала я строить запрос похоже, цепочкой равенств. Но ведь картина следующая: в одну поставку могут войти несколько товаров, принадлежащих разным подгруппам... то есть каждый из этапов сравнения мне, скорее всего, вернет множество записей. На этом месте MSSQL компилил, но ругался при выполнении, ибо в равенстве хотел видеть ровно 1 запись.
Поэтому подсказанный мне INNER JOIN  спас ситуацию.

[Sla]

дело в том что

select table1.*
from
  table1, table2
where
table1.id = table2.id

по сути и есть
inner join

select table1.*
from
  table1
  inner join table2 on table1.id = table2.id

другое дело left join и right join


upd я исравил ошибку в первом запросе

[Arinyshka]

ага... вот так понятнее а то я сидела над той ошибкой выполнения и не понимала, что делать

[Sla]

можешь выполнить?

Код:

SELECT    T_Group.PurpPay
from     PlatTerb, Treb, Base, T_Goods, T_SubGroup, T_Group
where 
    PlatTreb.id_PlTreb   = @tempIdTreb
and PlatTreb.id_treb     = Treb.id_treb
and Treb.id_postavka     = Base.id_postavka
and Base.id_goods        = T_Goods.id_goods
and T_Goods.id_SubGroup  = T_SubGroup.id_SubGroup
and T_SubGroup.id_Group  = T_Group.id_Group

а потом написать все с inner join

результат будет один и тот же


то что ты писала про DISTINCT все верно.

[Arinyshka]

выполнила. Работает Насколько я понимаю, от case мне не уйти - Ваш запрос выдает несколько разных строк, если они существуют. То есть либо успокоиться и проверять это уже в коде прожки, либо case в хранимой. Верно? Мне казалось, по "затратам" удобнее в хранимой - не гонять лишнее в датасетах. Опять-таки, могу быть неправа...
Ps А можно на "ты", да? а то статус "большой начальник" меня запугал

[Джон]

а то статус "большой начальник" меня запугал

) )

[Sla]

1юArinyshka,

А можно на "ты"

тюю, конечно можно
а "большой начальник" это брызги, ты не поверишь, но я еще больший начальник


конечно от CASE не уйти.

По поводу хранимости.
Толстый клиент - тонкий сервер  = датасет
тонкий клиент - толстый сервер = хранимая

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

Кроме того при сопровождении - удобней когда все на сервере.
Здесь решение за тобой.

[Dimka]

Если речь идёт всё же про SQL Server, то там получить номера строчек несложно. Поможет функция:

Код:

ROW_NUMBER() OVER(ORDER BY ...)

Другое дело, что это идеологически неправильное решение из-за неправильной постановки задачи

[Dimka]

Кроме того при сопровождении - удобней когда все на сервере.

Я бы сказал не "всё на сервере", а "всё в одном месте"

[Arinyshka]

я еще больший начальник

Здорово Люблю демократию

По поводу хранимости.
Толстый клиент - тонкий сервер  = датасет
тонкий клиент - толстый сервер = хранимая

Я, как обычно... сначала хватаюсь прогить, потом читаю... Я датасетом назвала те данные, которые перекидываются от сервера к клиенту по выполнениии хранимой. В корне неверно, да? Раньше грозили: уйду в монастырь. Похоже, я уйду в книжки

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

Кроме того при сопровождении - удобней когда все на сервере.
Здесь решение за тобой.

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

Если речь идёт всё же про SQL Server, то там получить номера строчек несложно. Поможет функция:

Код:

ROW_NUMBER() OVER(ORDER BY ...)

Другое дело, что это идеологически неправильное решение из-за неправильной постановки задачи

Слово "идеология" в Беларуси вызывает отвращение Впервые принимаю идеологически выдержанное решение  Справилась средствами Дельфы, это было несложно. Спасибо за совет 

[Sla]

Я вот чего подумал. Если у тебя есть доступ на create procedure, то у тебя, скорей всего, есть доступ и на create view
поэтому можно делать представления любой формы, под свои нужды.

[Arinyshka]

Угу. Нашла гуглом пример, почитала, что это такое... даже сварганила. А в каком случае целесообразно это использовать? Как-то у нас на сервере 234 таблицы, 1870 хранимых и всего 6 видов - значит, активно этим никто не пользовался. Например, мне можно было сделать вот такую виртуальную таблицу выстраданным селектом с ORDER BY и уже с ней работать? Например, таблицу строю без case - и смотрю количество строк в ней?
Сорри, что так много вопросов... как-то у нас в офисе и обратиться не к кому...

[Sla]

ну вот примерчик

Код:

Create View KL_T020 
AS 
Select Id, 
SubString(AddInfo,1,1) as T020 , 
SubString(Info,1,40) as TXT 
From RefferParam 
Where OwnerId = (Select R.Flag from RefferParam R Where R.OwnerId=-1 and R.Code="KL_T020")

[Arinyshka]

Тааак... получится таблица, содержащая востребованные части строк AddInfo и Info из таблицы RefferParam, такие что поле OwnerId удовлетворяет условию подзапроса... Уфф Пасиб, буду пытаться использовать

[Dimka]

Слово "идеология" в Беларуси вызывает отвращение

Может и зря. Всё познаётся в сравнении Впрочем, offtop.