MS SQL2005, хитрый (для меня) запрос
От: newb  
Дата: 22.12.05 09:47
Оценка:
Есть три таблицы:
1. Actions: поля: ActionID (PK), Name
2. Groups: поля: GroupID (PK), Name
3. И связка между ними: Grants: поля: ActionID (FK на PK табл. Actions), GroupID (FK на PK табл. Groups), Period.
ActionID + GroupID — это PK для этой таблицы.


Нужен запрос (желательно без подзапросов) который для заданной группы будет делать следущее:
Для ВСЕХ записей из Actions выводит период (из табл. Grants, если есть запись для данного ActionID и GroupID, либо NULL)

Например:
Пусть в таблицах будут следующие данные:
1. Actions: {'A1', 'AAA'}, {'A2', 'BBB'}, {'A3', 'CCC'}, {'A4', 'DDD'}
2. Groups: {'G1', 'Group1'}, {'G2', 'Group2'}, {'G3', 'Group3'}
3. Grants: {'A1', 'G1', 123 }, {'A2', 'G2', 345}, {'A3', 'G2', 555}, {'A4', 'G3', 234}

Нужно получить след. рез-т:
Для 'G1' (GroupID идет параметром запроса):
'A1', 'AAA', 123
'A2', 'BBB', NULL
'A3', 'CCC', NULL
'A4', 'DDD', NULL

для 'G2' результат такой:
'A1', 'AAA', NULL
'A2', 'BBB', 345
'A3', 'CCC', 555
'A4', 'DDD', NULL

для 'G2' результат такой:
'A1', 'AAA', NULL
'A2', 'BBB', NULL
'A3', 'CCC', NULL
'A4', 'DDD', 234
Re: MS SQL2005, хитрый (для меня) запрос
От: BilliKillers  
Дата: 22.12.05 10:03
Оценка:
Здравствуйте, newb, Вы писали:

N>Есть три таблицы:
N>1. Actions: поля: ActionID (PK), Name
N>2. Groups: поля: GroupID (PK), Name
N>3. И связка между ними: Grants: поля: ActionID (FK на PK табл. Actions), GroupID (FK на PK табл. Groups), Period.
N>ActionID + GroupID — это PK для этой таблицы.


N>Нужен запрос (желательно без подзапросов) который для заданной группы будет делать следущее:
N>Для ВСЕХ записей из Actions выводит период (из табл. Grants, если есть запись для данного ActionID и GroupID, либо NULL)

N>Например:
N>Пусть в таблицах будут следующие данные:
N>1. Actions: {'A1', 'AAA'}, {'A2', 'BBB'}, {'A3', 'CCC'}, {'A4', 'DDD'}
N>2. Groups: {'G1', 'Group1'}, {'G2', 'Group2'}, {'G3', 'Group3'}
N>3. Grants: {'A1', 'G1', 123 }, {'A2', 'G2', 345}, {'A3', 'G2', 555}, {'A4', 'G3', 234}

N>Нужно получить след. рез-т:
N>Для 'G1' (GroupID идет параметром запроса):
N>'A1', 'AAA', 123
N>'A2', 'BBB', NULL
N>'A3', 'CCC', NULL
N>'A4', 'DDD', NULL

N>для 'G2' результат такой:
N>'A1', 'AAA', NULL
N>'A2', 'BBB', 345
N>'A3', 'CCC', 555
N>'A4', 'DDD', NULL

N>для 'G2' результат такой:
N>'A1', 'AAA', NULL
N>'A2', 'BBB', NULL
N>'A3', 'CCC', NULL
N>'A4', 'DDD', 234

select Actions.*, Grants.Period
from Actions left outer join Grants on Actions.ActionID = Grants.ActionID
where Grants.GroupID = @myGroupId
Re: MS SQL2005, хитрый (для меня) запрос
От: protected  
Дата: 22.12.05 10:12
Оценка:
Здравствуйте, newb, Вы писали:

N>Есть три таблицы:
N>1. Actions: поля: ActionID (PK), Name
N>2. Groups: поля: GroupID (PK), Name
N>3. И связка между ними: Grants: поля: ActionID (FK на PK табл. Actions), GroupID (FK на PK табл. Groups), Period.
N>ActionID + GroupID — это PK для этой таблицы.


N>Нужен запрос (желательно без подзапросов) который для заданной группы будет делать следущее:
N>Для ВСЕХ записей из Actions выводит период (из табл. Grants, если есть запись для данного ActionID и GroupID, либо NULL)


SELECT 
    Actions.ActionID, Actions.Name, Grants.Period
FROM 
    Actions
    LEFT OUTER JOIN Grants INNER JOIN Groups ON Grants.GroupID = Grants.GroupID ON Actions.ActionID = Grants.ActionID
WHERE
    Groups.GroupID = @GroupID


где @GroupID — ваш параметр
Re[2]: MS SQL2005, хитрый (для меня) запрос
От: protected  
Дата: 22.12.05 10:14
Оценка:
Здравствуйте, protected, Вы писали:

P>
P>SELECT 
P>    Actions.ActionID, Actions.Name, Grants.Period
P>FROM 
P>    Actions
P>    LEFT OUTER JOIN Grants INNER JOIN Groups ON Grants.GroupID = Grants.GroupID ON Actions.ActionID = Grants.ActionID
P>WHERE
P>    Groups.GroupID = @GroupID
P>


P>где @GroupID — ваш параметр

Да, первый ответ был более верный, INNER JOIN с Groups ненужен.
Re[2]: MS SQL2005, хитрый (для меня) запрос
От: newb  
Дата: 22.12.05 10:54
Оценка:
Здравствуйте, BilliKillers, Вы писали:

N>>Нужно получить след. рез-т:
N>>Для 'G1' (GroupID идет параметром запроса):
N>>'A1', 'AAA', 123
N>>'A2', 'BBB', NULL
N>>'A3', 'CCC', NULL
N>>'A4', 'DDD', NULL

BK>
BK>select Actions.*, Grants.Period
BK>from Actions left outer join Grants on Actions.ActionID = Grants.ActionID
BK>where Grants.GroupID = @myGroupId
BK>


Получил след. рез-т:
Для 'G1' (GroupID идет параметром запроса):
'A1', 'AAA', 123
что несовсем то, что требовалось.

Сваял такое:
declare @GID as varchar(10)
set @GID = 'G1'
--set @GID = 'G2'
--set @GID = 'G3'

select a.id, a.Caption, g.Grants /*, grp.Name*/ from Groups grp
inner join Grants g on grp.GroupID = g.GroupID and g.GroupID = @GID
right join Actions a on a.id = g.ActionID

Вот только вьюху не сделаешь из этого
Re[3]: MS SQL2005, хитрый (для меня) запрос
От: newb  
Дата: 22.12.05 10:58
Оценка:
Здравствуйте, newb, Вы писали:

Не исправил рабочий код немного
N>
N>select a.id, a.Caption, g.Period /*, grp.Name*/ from Groups grp
N>inner join Grants g on grp.GroupID = g.GroupID and g.GroupID = @GID
N>right join Actions a on a.id = g.ActionID
N>
Re[4]: MS SQL2005, хитрый (для меня) запрос
От: Ромашка Украина  
Дата: 22.12.05 11:28
Оценка:
Здравствуйте newb, Вы писали :

Мама...

select Actions.*, Grants.Period
from Actions
    left join Grants on Actions.ActionID = Grants.ActionID
--where Grants.GroupID = @myGroupId
Posted via RSDN NNTP Server 2.0


Всё, что нас не убивает, ещё горько об этом пожалеет.
Re: MS SQL2005, хитрый (для меня) запрос
От: newb  
Дата: 23.12.05 03:15
Оценка:
Спасибо всем.
Все ваши варианты практически одно и тоже, поэтому отвечу всем сразу.
Одного join`a мало. Думал, что где-то ошибся при "рисовании" примера.
Создал сейчас три таблицы, заполнил, как указано ниже:

N>Например:
N>Пусть в таблицах будут следующие данные:
N>1. Actions: {'A1', 'AAA'}, {'A2', 'BBB'}, {'A3', 'CCC'}, {'A4', 'DDD'}
N>2. Groups: {'G1', 'Group1'}, {'G2', 'Group2'}, {'G3', 'Group3'}
N>3. Grants: {'A1', 'G1', 123 }, {'A2', 'G2', 345}, {'A3', 'G2', 555}, {'A4', 'G3', 234}

N>Нужно получить след. рез-т:
N>Для 'G1' (GroupID идет параметром запроса):
N>'A1', 'AAA', 123
N>'A2', 'BBB', NULL
N>'A3', 'CCC', NULL
N>'A4', 'DDD', NULL

N>для 'G2' результат такой:
N>'A1', 'AAA', NULL
N>'A2', 'BBB', 345
N>'A3', 'CCC', 555
N>'A4', 'DDD', NULL

N>для 'G3' результат такой:
N>'A1', 'AAA', NULL
N>'A2', 'BBB', NULL
N>'A3', 'CCC', NULL
N>'A4', 'DDD', 234

declare @GID as varchar(10)
set @GID = 'G1'
--set @GID = 'G2'
--set @GID = 'G3'

select Actions.*, Grants.Period
from Actions left outer join Grants on Actions.ActionID = Grants.ActionID
where Grants.GroupID = @GID



Рузультат для 'G1':
'A1', 'AAA', 123

Рузультат для 'G2':
'A2', 'BBB', 345
'A3', 'CCC', 555

Рузультат для 'G3':
'A4', 'DDD', 234

Что не совсем то,что хотелось.
Мой вчерашний вариант:
select a.ActionID, a.Name, g.Period /*, grp.Name*/ from Groups grp
inner join Grants g on grp.GroupID = g.GroupID and g.GroupID = @GID
right join Actions a on a.ActionID = g.ActionID

работает, но вьюху из него не сделаешь. Вьюха не обязательное условие, можно было бы остановиться на приведенном выше варианте, но поднапрягшись сваял такое:
SELECT a.ActionID, a.Name, grp.GroupID, ISNULL(g.Period, 0) AS Period
FROM Actions a
CROSS JOIN
  (SELECT GroupID, Name
   FROM Groups
  ) grp
LEFT OUTER JOIN Grants g ON a.ActionID = g.ActionID AND grp.GroupID = g.GroupID


Результат такой:
A1 AAA G1 123
A2 BBB G1 0
A3 CCC G1 0
A4 DDD G1 0

A1 AAA G2 0
A2 BBB G2 345
A3 CCC G2 555
A4 DDD G2 0

A1 AAA G3 0
A2 BBB G3 0
A3 CCC G3 0
A4 DDD G3 234


Вот сейчас

Мама...

Re: MS SQL2005, хитрый (для меня) запрос
От: U-4X-96  
Дата: 23.12.05 05:00
Оценка: 
SELECT "Actions"."ActionID", "Actions"."Name", "Grants"."Period"
FROM "Actions" LEFT JOIN "Grants" ON ("Actions"."ActionID"="Grants"."ActionID")
WHERE "Actions"."GroupID"=@p
Re[2]: MS SQL2005, хитрый (для меня) запрос
От: U-4X-96  
Дата: 23.12.05 05:10
Оценка:
Упс ответов вначале не заметил,
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.