Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
Takes
Member [заблокирован]

Откуда:
Сообщений: 5
Есть таблица с товарами в виде ID Имя_Товара и таблица со складами вида ID
А теперь пример
IDИмя_Товара
1 Щипцы
2 Ножницы

и таблица складов
ID
1
2

Теперь я создал третью таблицу в которой хранится остатки.
ID_ТовараID_Склада Количество
111000
122000

Во первых, я немного не разобрался со связью многое ко многим, точнее её смысл. По этому таблица без индексов.
Во вторых мне нужен запрос который будет давать такой результат
ID_ТовараИмя_ТовараID_Склада Количество
1Щипцы11000
1Щипцы22000
2Ножницы1NULL
2Ножницы2NULL

Уже вторые сутки долбаюсь, ничего не выходит. Помогите плиз)
Подозреваю, что проблема именно в том, что связь многое-ко-многим не реализована, а нужна ли она в этой ситуации?
20 июл 13, 18:52    [14593670]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
select
 t.ID as ID_Товара,
 t.Имя_Товара,
 s.ID as ID_Склада,
 o.Количество 
from
 Товар t cross join
 Склад s left join
 Остатки o on o.ID_Товара = t.ID and o.ID_Склада = s.id;
20 июл 13, 19:06    [14593686]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
KRS544
Member

Откуда:
Сообщений: 497
Именно такой таблицы вы не получите.
У вас в остатках нет ножниц.

А так, если название склада не нужно (у вас его и нет в таблице), то

select
o.ID_Товара,
t.Имя_Товара,
o.ID_Склада,
o.Количество
from
Товар t inner join
Остатки o on o.ID_Товара = t.ID
20 июл 13, 19:13    [14593700]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
KRS544
Member

Откуда:
Сообщений: 497
сорри, не сразу понял условие.
Тогда как у invm, но зачем нужно такое перечисление?
Да и кросс джойн при больших объемах не есть хорошо...
20 июл 13, 19:28    [14593726]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
KRS544
Member

Откуда:
Сообщений: 497
Решил все-таки написать.
С точки зрения построения учетной системы будут лучше, если в таблице Остатки вы будете хранить все варинты
т. е.

ID_Товара ID_Склада Количество
1 1 1000
1 2 2000
2 1 0
2 2 0

Тогда:
1. будет работать инер джойн
2. при поставке или выдаче товара вы не будете вставлять или удалять строки, а будете делать апдейт колонки количество
3. есть и другие плюсы
20 июл 13, 19:42    [14593756]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
Takes
Member [заблокирован]

Откуда:
Сообщений: 5
KRS544
Решил все-таки написать.
С точки зрения построения учетной системы будут лучше, если в таблице Остатки вы будете хранить все варинты
т. е.

ID_Товара ID_Склада Количество
1 1 1000
1 2 2000
2 1 0
2 2 0

Тогда:
1. будет работать инер джойн
2. при поставке или выдаче товара вы не будете вставлять или удалять строки, а будете делать апдейт колонки количество
3. есть и другие плюсы

Спасибо за ваш совет, но каким тогда образом, так сказать автоматизировать наличие в этой таблице данных. То есть будут товары например, для которых ещё не задан остаток, как сделать, что бы он был дефолтно нулевой???
20 июл 13, 20:09    [14593806]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Ответьте плиз кто нибудь, каким тогда образом, так сказать автоматизировать наличие в этой таблице данных. То есть будут товары например, для которых ещё не задан остаток, как сделать, что бы он был дефолтно нулевой???
20 июл 13, 22:19    [14594093]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Kimel,

Триггер, например.

Но лучше подумайте, зачем вам видеть отсутствие товаров на всех складах, где их нет. Обычно задача стоит несколько иначе - это один из двух вариантов:

1. Найти конкретный товар везде, где он есть. Тогда нам в общем случае неинтересны склады, где его нет.
2. Найти все товары на конкретном складе. Тогда нам тем более неинтересны другие склады.

В обоих случаях можно обойтись left join, чтобы добавить отсутствующие позиции. А видеть везде все, что есть и все, чего нет... зачем?
21 июл 13, 10:15    [14594414]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
KRS544
Member

Откуда:
Сообщений: 497
делаешь триггер на инсерт в таблицу товаров

insert into остатки
select ID.код товара , ID склада
from inserted cклад cross join Склады

на делит

delete from остатки
where id.код товара in (select id from deleted)

то же самое на таблицу складов

правильный синтаксис сам сделаешь
но предыдущий совет тоже верный, лучше пересмотреть концепцию
21 июл 13, 11:53    [14594495]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
Takes
Member [заблокирован]

Откуда:
Сообщений: 5
Это для самой программы. Если он не увидит запись по конкретному товару в конкретном складе, как она сможет управлять его количеством?
Просто я не хочу везде лепить условие типа, если запись существе то -, если нет то -
21 июл 13, 18:42    [14595463]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
может быть надо что-то вроде этого?
+ Создание таблиц
use TestDB;
if object_id('Товар') is null begin
   create table Товар(id int primary key, name varchar(50), Количество float, check(Количество>=0))
end
if object_id('Склад') is null begin
   create table Склад(id int primary key, name varchar(50))
end
if object_id('Остатки') is null begin
   create table Остатки(ID_Товара int, ID_Склада int, Количество float, primary key (ID_Товара, ID_Склада), check(Количество>=0))
   create /*unique*/ nonclustered index IX_Остатки on dbo.Остатки (ID_Склада,ID_Товара) include(Количество)
end
GO
+ Процедура обработки поступления товара на склад
-- поступление товара на склад
CREATE PROCEDURE Товар_поступил @ID_Товара INT, @ID_Склада INT, @Количество FLOAT
AS
BEGIN TRANSACTION

MERGE Товар m
USING (VALUES (@ID_Товара, @ID_Склада, @Количество)) s(idt, ids, qty)
   ON s.idt = m.id
WHEN MATCHED THEN
   UPDATE SET m.Количество += s.qty
WHEN NOT MATCHED BY target THEN 
   INSERT (id, Количество) VALUES (s.idt, s.qty);
IF @@ERROR != 0 GOTO err

IF NOT EXISTS (SELECT * FROM Склад WHERE id = @ID_Склада)
BEGIN
   INSERT Склад (id) VALUES (@ID_Склада)
   IF @@ERROR != 0 GOTO err
END

MERGE Остатки m
USING (VALUES (@ID_Товара, @ID_Склада, @Количество)) s(idt, ids, qty)
   ON s.idt = m.ID_Товара AND s.ids = m.ID_Склада
WHEN MATCHED THEN
   UPDATE SET m.Количество += s.qty
WHEN NOT MATCHED BY target THEN
   INSERT (ID_Товара, ID_Склада, Количество) VALUES (s.idt, s.ids, s.qty);
IF @@ERROR != 0 GOTO err

COMMIT TRANSACTION
RETURN 0

err:
ROLLBACK TRANSACTION
declare @qty varchar(256)=convert(varchar,@Количество)
RAISERROR (N'Ошибка изменения количества товара %d на складе %d. Неверное количество %s', 10,1, @ID_Товара, @ID_Склада, @qty) with nowait;
RETURN - 1
GO
+ Добавление информации о поступлении товара
exec Товар_поступил 17,2,10 
exec Товар_поступил 17,5,20
exec Товар_поступил 9,2,5
exec Товар_поступил 17,5,-15
exec Товар_поступил 9,2,-15

select 'Товар'[Товар],* from Товар
select 'Склад'[Склад],* from Склад
select 'Остатки'[Остатки],* from Остатки
ТоварidnameКоличество
Товар9NULL5
Товар17NULL15
Складidname
Склад2NULL
Склад5NULL
ОстаткиID_ТовараID_СкладаКоличество
Остатки925
Остатки17210
Остатки1755
+ отчет по складам о наличии товара
-- отчет по складам о наличии товара
select 
   s.id ID_Склад, s.name Склад, 
   t.id ID_Товара, t.name Товар, 
   o.Количество
from Склад s 
join Остатки o on o.ID_Склада=s.id
join Товар t on t.id=o.ID_Товара
where o.Количество>0
order by s.id, t.id
ID_СкладСкладID_ТовараТоварКоличество
2NULL9NULL5
2NULL17NULL10
5NULL17NULL5

Хотя неясно, куда пристегнуть поставщиков товара, потребителей товара, даты операций и построение отчета на заданную дату
21 июл 13, 22:16    [14596232]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
Takes
Это для самой программы. Если он не увидит запись по конкретному товару в конкретном складе, как она сможет управлять его количеством?
Просто я не хочу везде лепить условие типа, если запись существе то -, если нет то -


бред какой-то. зачем управлять тем чего нету? нет, если хотите ВСЕ, то как вам сказали "lef join" (только isnull не забудьте). Но чем вас не устраивает обычный подход - в программе датасет по существующим складам, датасет по существующим товарам, датасет по существующим остаткам - и управляете как хотите?
22 июл 13, 10:15    [14597382]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать запрос из 3 таблиц со связью Многое-ко-многим  [new]
fktrctq906
Guest
Вам нужны все товары и остатки по ним, в случае отсутсвия - чтоб выводился NULL? Классический случай для left join, либо я чего то не понял. Объясните, почему вариант с ним не подходит
22 июл 13, 10:26    [14597425]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить