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

Откуда: Minsk Power Station
Сообщений: 497
Подскажите пожалуйста как выбрать все поля таблицы @Sensors, и значения из @SensorValue, если там таковые имеются.
Получается получить только поля, у которых есть значение. А нужны все поля.
--список всех датчиков
declare @Sensors table([Name] varchar(10), id int)
insert into @Sensors(name, id)
select 'Датчик 1', 1 union all
select 'Датчик 2', 2 union all
select 'Датчик 3', 3  

--таблица значений дачиков
declare @SensorValue table(ID int, SensorID int, SensorValue nvarchar(10))
insert into @SensorValue(ID, SensorID, SensorValue)
select 1, 1, 'Запущен' union all
select 2, 3, 'Остановлен' 


в результат нужно вывести названия всех датчиков, независимо, есть у него значение или нет. если нет, то выводить NULL или пустую строку. Нужно, чтобы получился вот такой результат:
Name,	SensorValue
Датчик 1 'Запущен'
Датчик 2 NULL
Датчик 3 'Остановлен'
19 авг 18, 02:43    [21646997]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
Собиратель мифов
Member

Откуда:
Сообщений: 10
SELECT, LEFT OUTER JOIN, ORDER BY.
19 авг 18, 09:18    [21647039]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
select s.Name
     , s.id
     , v.SensorValue
from @Sensors s
	left join @SensorValue v on s.id = v.SensorID
order by s.Name
19 авг 18, 10:53    [21647057]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 497
Владимир Затуливетер, большое вам спасибо!

Работало, пока не пришлось усложнить условия. Просьба ещё раз взглянуть и посоветовать решение.
В первой таблице перечислены датчики определённых механизмов. У каждого механизма есть свой набор датчиков:
--список всех датчиков механизмов
declare @Sensors table([Name] varchar(10), id int, mechanism int)
insert into @Sensors(name, id, mechanism)
select 'Датчик 1', 1, 1 union all
select 'Датчик 2', 2, 1 union all
select 'Датчик 3', 3, 1 union all
select 'Датчик 7', 4, 2 union all
select 'Датчик 9', 5, 2  

--таблица наборов со значениями дачиков
declare @SensorValue table(SensorID int, SensorValue nvarchar(10), SetID int)
insert into @SensorValue(SensorID, SensorValue, SetID)
select 1, 'Запущен', 1 union all
select 3, 'Остановлен', 1 union all
select 3, 'Остановлен', 2 union all
select 4, 'Запущен', 2


а во второй таблице содержатся значения и номера наборов этих значений.
теперь я хочу получить значения набора данных №2 (SetID=2) всех датчиков механизма №1 (Mechanism=1), делаю такое условие:
select s.Name
     , s.id
     , v.SensorValue
	 , v.SetID
from @Sensors s
	left join @SensorValue v on s.id = v.SensorID
where v.SetID = 2 and s.mechanism = 1	
order by s.[Name]

а в ответ:
Name		id	SensorValue	SetID
Датчик 3 3 Остановлен 2

а как добиться, чтобы выводились все датчики этого механизма, даже если по каким-либо из них нет значения

Name id SensorValue SetID
Датчик 1 1 NULL NULL
Датчик 2 2 NULL NULL
Датчик 3 3 Остановлен 2
??

или же, если условие такое:
where v.SetID = 2 and s.mechanism = 2
то такой результат:

Name id SensorValue SetID
Датчик 7 4 Запушен 2
Датчик 9 1 NULL NULL
19 авг 18, 16:31    [21647252]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
palladin600,

перенесите условия, накладываемые на поля правой таблицы LEFT JOINа из WHERE в ON.
В данном случае
and s.mechanism = 1
19 авг 18, 16:42    [21647254]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 497
iap, что ничего не клеится, уже всякие джойны перепробовал, выдаёт не то что надо
select s.Name
     , s.id
     , v.SensorValue
	 , v.SetID
from @Sensors s
	right join @SensorValue v on s.id = v.SensorID and s.mechanism = 2 
	where v.SetID = 2
order by s.[Name]

выдаёт:
Name		id	SensorValue	SetID
NULL NULL Остановлен 2
Датчик 7 4 Запущен 2

а нужно:
Name		id	SensorValue	SetID
Датчик 7 4 Запушен 2
Датчик 9 5 NULL NULL
19 авг 18, 17:34    [21647270]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
palladin600,

Думать нужно, а не гадать
select
 s.Name, s.id, sv.SensorValue, sv.SetID
from
 @Sensors s left join
 @SensorValue sv on sv.SensorID = s.id
where
 s.mechanism = 2;
19 авг 18, 18:27    [21647296]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 497
invm
palladin600,

Думать нужно, а не гадать


Ну это я с удовольствием. А когда не выходит, то что поделать...
Вот и в вашем совете-примере, если поставить условие s.mechanism = 1, то выдаст почему-то задвоенные данные.
Name		id	SensorValue	SetID
Датчик 1 1 Запущен 1
Датчик 2 2 NULL NULL
Датчик 3 3 Остановлен 1
Датчик 3 3 Остановлен 2
тут выдал ещё последнюю запись где SetID = 2, а надо только где SetID = 1,

как отфильтровать?
сделал так:
select
 s.Name, s.id, sv.SensorValue, sv.SetID
from
 @Sensors s left join
 @SensorValue sv on sv.SensorID = s.id
where
 s.mechanism = 1 and (sv.SetID = 1 or sv.SetID is null);


работает, но тогда, если поставить фильтр SetID = 2, то выдаёт результаты странные:
Name		id	SensorValue	SetID
Датчик 2 2 NULL NULL
Датчик 3 3 Остановлен 2


а нужно:

Name id SensorValue SetID
Датчик 1 1 NULL NULL
Датчик 2 2 NULL NULL
Датчик 3 3 Остановлен 2

Спасибо большое!
19 авг 18, 19:43    [21647326]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
palladin600
если поставить условие s.mechanism = 1, то выдаст почему-то задвоенные данные.
Потому что в @SensorValue две строки для "Датчик 3".
Сервер не умеет читать ваши мысли. А вы, пока что, не можете внятно сформулировать задачу.
19 авг 18, 20:10    [21647340]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 497
Сорри, что запарил уже,
Извините, вот по полочкам разложу.
Есть устройства (mechanism), у которых есть свои наборы датчиков (sensors).

И есть таблица, в которой содержатся настройки этих датчиков.

Нужно получить набор значений датчиков для определённого устройства.

Т.е., предположим, что есть Устройство1, у которого прописано 3 датчика: Датчик1-3. К этому устройству есть также готовый набор значений (SetID) для датчиков.

Я хочу выбрать определённое устройство, и посмотреть значения по выбранному набору настроек.

Если разбирать последовательно задачу, то я должен вначале получить список всех датчиков интересующего меня устройства из таблицы @Sensors, где:
Mechanism = 1
Датчик1
Датчик2
Датчик3
иными словами, я могу выбрать только одно устройство для отчёта.

затем, я получаю значения конкретного набора значений для датчиков этого устройства из таблицы @SensorValue, где:
SetID = 1
Датчик1 = 'Запушен'
Датчик2 = 'Остановлен'

и сопоставляю эти значения с датчиками устройства:
For Each Sensor In Mechanism.Sensors
	If Sensor.Name = SensorValue.Name
		'если id датчика совпадает, то уставливаем значение
		Mechanism.Sensor.Value = SensorValue("Датчик1").SensorValue
	End if
Next

(цикл приведён примитивный, для наглядности)

в итоге, я получаю картину, что для Устройства1:
для Датчик1 есть значение равное 'Запущен'
для Датчик2 не нашлось значения, т.е. NULL
для Датчик3 есть значение равное 'Остановлен'


если выставить условие SetID = 2, то получим уже другой набор значений датчиков для устройства (Mechnanism = 1)
для Датчик1 не нашлось значения, т.е. NULL
для Датчик2 не нашлось значения, т.е. NULL
для Датчик3 есть значение равное 'Остановлен'

и, если бы я ввёл несуществующй набор, скажем SetID = 3, то в ответ бы получил просто список датчиков с отсутсвующими значениями:
для Датчик1 не нашлось значения, т.е. NULL
для Датчик2 не нашлось значения, т.е. NULL
для Датчик3 не нашлось значения, т.е. NULL

Может двумя запросами это можно сделать, но список датчиков должен быть всегда, независимо, есть набор значений или же нету. Или же, может перенести/добавить нужно поле какое-то в таблицу, чтобы по-правильному, как говорится.
Ну вот в общем, такие дела. Конечно, можно двумя запросами делать, и потом городить на клиенте цикл с сопоставлениями, но так хочется по-грамотному через t-sql реализовать.
20 авг 18, 01:27    [21647488]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
aleks222
Member

Откуда:
Сообщений: 855
Вникни в понятие Derived Table.

with s as ( select * from @Sensors where mechanism = 1 )
    , sv as ( select * from @SensorValue  where SetID = 1 )
select
 s.Name, s.id, sv.SensorValue, sv.SetID
from  s left join sv on sv.SensorID = s.id
;
20 авг 18, 07:06    [21647526]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
Посетитель
Member

Откуда:
Сообщений: 1384
ну или то же самое без cte

select s.Name, s.id, sv.SensorValue, sv.SetID
  from @Sensors s 
  left join @SensorValue sv 
    on sv.SensorID = s.id
   and sv.SetID = 1
 where s.mechanism = 1;
20 авг 18, 08:08    [21647548]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
aleks222
Member

Откуда:
Сообщений: 855
Посетитель
ну или то же самое без cte

select s.Name, s.id, sv.SensorValue, sv.SetID
  from @Sensors s 
  left join @SensorValue sv 
    on sv.SensorID = s.id
   and sv.SetID = 1
 where s.mechanism = 1;


Не учи плохому. Без СТЕ надо так

select
 s.Name, s.id, sv.SensorValue, sv.SetID
from  ( select * from @Sensors where mechanism = 1 )
 as s left join ( select * from @SensorValue  where SetID = 1 ) as sv on sv.SensorID = s.id
;
20 авг 18, 08:31    [21647556]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
Посетитель
Member

Откуда:
Сообщений: 1384
aleks222
Посетитель
ну или то же самое без cte

select s.Name, s.id, sv.SensorValue, sv.SetID
  from @Sensors s 
  left join @SensorValue sv 
    on sv.SensorID = s.id
   and sv.SetID = 1
 where s.mechanism = 1;


Не учи плохому. Без СТЕ надо так

select
 s.Name, s.id, sv.SensorValue, sv.SetID
from  ( select * from @Sensors where mechanism = 1 )
 as s left join ( select * from @SensorValue  where SetID = 1 ) as sv on sv.SensorID = s.id
;


слушаюсь и повинуюсь, о мой господин!
или нет.
20 авг 18, 08:36    [21647560]     Ответить | Цитировать Сообщить модератору
 Re: Выборка всех полей основной таблицы, даже если нет значений у вспомогательной  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 497
Друзья, большое спасибо!

Заскрипело приложеньице-то!

Все три варианта решений от 'aleks222' и 'Посетитель' выдали то, что нужно.
И с СТЕ и без.

Спасибо за квалифицированную помощь.
20 авг 18, 11:05    [21647826]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить