JOIN do tabeli ,jeśli mamy NULL

Cześć,
Posiadam kilka tabel w MS SQL które tworzą kalendarz, łącząc 3 tabele jestem wstanie wyświetlić czas dla każdej zmiany jaki jest przypisany w danym dniu dla wybranej przez użytkownika maszyny.

SELECT d.*,AvailableTime
FROM(
	SELECT  s.*, CalendarShiftId
	FROM( 
  		SELECT 
		Date, 	CalendarDayId, 	VehicleId ,CompanyId ,DepartmentId ,IsDeleted
		FROM [TEST].[dbo].[Calendar]
		WHERE VehicleId   IN('2','5') 
		AND Date BETWEEN '2021-07-01 00:00:00.000' AND '2021-07-01 23:00:00.000'  
		AND IsDeleted =0
		) s 
	JOIN  [TEST].[dbo].[CalendarDayShift] ON CalendarDayShift.CalendarDayId = S.CalendarDayId
	)d
jOIN [TEST].[dbo].[CalendarShift] ON CalendarShift.Id = d.CalendarShiftId

i otrzymuje wynik :

Tak wygląda cały algorytm:

Mam problem iż w tabeli Calendar numer urządzenia może być NULL i wtedy program powinien przejść do tabeli Vahicle i sprawdzić jaki numer DepartmentId odpowiada VehiclId, analogicznie jest gdy DepartmentId jest Null powinien sprawdzić CompanyId i dopiero wtedy odczytać CalendarDayId dla wybranego na samym początku VehicleId i przejść dalej( co już zrobiłem).
Lecz zawsze z priorytetem VehiclId później DepartmentId a na samym końcu jak reszta jest Null dopiero CompanyId. Na zasadzie gdy VehicleId nie jest NULL to reszty już nie bierzemy pod uwagę.

Próbując to zrobić przy użyciu:

 CASE WHEN VehicleId IS NULL THEN [Vehicle].VehicleId  ELSE Calendar.VehicleId END 

wyrzuca błąd : The multi-part identifier "Vehicle.VehicleId" could not be bound.
Ktoś mógł by podpowiedzieć jak to rozwiązac taki problem ?

wg mnie jest prosty błąd....

CASE WHEN [TUTAJ].VehicleId IS NULL THEN [Vehicle].VehicleId ELSE Calendar.VehicleId END

W miejscu słowa TUTAJ wpisz odpowiednią tabelę - wydaje mi się, że powinno to być Calendar.

Strasznie nieintuicyjne to zapytanie które zrobiłeś, dlaczego to jest w podzapytaniach, nie łatwiej

SELECT 
  c.Date
  ,c.CalendarDayId
  ,c.VehicleId 
  ,c.CompanyId 
  ,c.DepartmentId 
  ,c.IsDeleted
  ,cds.CalendarShiftId
  ,cs.AvailableTime
FROM 
  [TEST].[dbo].[Calendar] c
  JOIN  [TEST].[dbo].[CalendarDayShift] cds ON cds.CalendarDayId = c.CalendarDayId
  jOIN [TEST].[dbo].[CalendarShift] cs ON cs.Id = cds.CalendarShiftId
WHERE 
  c.VehicleId   IN('2','5') 
  AND c.Date BETWEEN '2021-07-01 00:00:00.000' AND '2021-07-01 23:00:00.000'  
  AND c.IsDeleted =0

Trochę łatwiej się zorientować, co się z czym łączy bez rokminiania zagnieżdżen zapytań.

Co do reszty pytania, to nie bardzo rozumiem, bo wg. danych z tabeli vehicle, które pokazałeś zarówno companyid jak i departmentid ma po kilka vehicleid, to chcesz uzupełnić jednym, czy kilkoma?

Mam problem iż w tabeli Calendar numer urządzenia może być NULL i wtedy program powinien przejść do tabeli Vahicle i sprawdzić jaki numer DepartmentId odpowiada VehiclId, analogicznie jest gdy DepartmentId jest Null powinien sprawdzić CompanyId i dopiero wtedy odczytać CalendarDayId dla wybranego na samym początku VehicleId i przejść dalej( co już zrobiłem).

Szwagier kolaska?

Użytkownik może tylko wybrać numer urządzenia od 1 do 10(VehicleId), może wybrać jedno urządzenie lub kilka.
Ustawiając kalendarz ustawiamy godziny pracy dla całej firmy, dla oddziału lub konkretnej maszyny, i dlatego jeśli ktoś ustawił kalendarz taki sam dla całej firmy to dla kolumn odział (DepartmentId) i dla maszyny (VehicleId) w tabeli mamy NULL jak na obrazku:

Ale jeśli ktoś ustawił dla jednej maszyny indywidualny czas to w żadnej kolumnie nie będzie NULL.

Mamy otrzymać tylko jeden wynik.
Sprawdzamy czy dla wybranego urządzenia mamy odpowiedni numer w kolumnie (VehicleId), jeśli tak to sprawdzamy CalendarDayId i przechodzimy dalej (taki scenariusz jest już zrobiony).
W takim przypadku już nie sprawdzamy reszty.
Ale problem pojawia się, jeśli na danym urządzeniu nie został ustawiony indywidualny czas tylko dla całego oddziału, w takim przypadku VehicleId mamy NULL i musimy sprawdzić w jakim oddziale jest wybrane urządzenie z tabeli Vehice, i na podstawie numeru oddziału odczytać CalendarDayId w tabeli Calendar i przejść dalej.
Też już nie sprawdzamy po CompanyId ponieważ już mamy numer oddziału.

Ciagle mi się to nie spina:

Mamy otrzymać tylko jeden wynik.

musimy sprawdzić w jakim oddziale jest wybrane urządzenie z tabeli Vehice

W przykładzie masz null dla vehicleid w departmentid = 4,

a wtabeli vehice wg. screenu są 2 rekordy z tym departmentem o vehicleid 1 i 2
To jak mam zwrócić jeden wynik?

Tak, ponieważ w odziale DepartmentID = 4 pracują dwie maszyny VehicleId =1 i VehicleId =2
i każda z tych maszyn może mieć inny czas pracy, dlatego najpierw musimy sprawdzić po VehicleId.

Jeśli VehicleId jest NULL sugeruje to że dla wszystkich maszyn w danym oddziale jest ustawiony taki sam czas pracy.

w tym przypadku widzimy że, VehicleId=2 ma ustawiony indywidualny czas pracy i CalendarDayId = 3 , (i to tylko bierzemy pod uwagę resztę pomijamy jesli już mamy VehicelId)
a
VehicleId=1 nie ma ustawionego indywidualnego czasu i dlatego ma NULL i dla niego musimy CalendarDayId sprawdzić po numerze oddziału.

Większość urządzeń będzie miała ma ustawiony czas względem firmy (czyli DepartmentId i VehicleId będzie NULL i tylko CompanyId będzie 1), ale niestety zdażają się takie wyjatki odnośnie oddziałów i konkretnych urządzeń.

Wrzucaj trochę większe te screeny bo strasznie są niewyraźnie.

Nadal to dla mnie zagmatwane, jeżeli weźmiemy calendardayid=3 to mam tam wpis tylko z comapnyid, tylko z companyid i departmentid, to suma sumarum muszę pobrać wszystkie maszyny z tej firmy?

Wiem dlatego nie mogę tego zrobić.
Użytkownik może wybrać tylko numer VehicleId, a ktoś był leniwy i dla całego oddziału ustawił taki sam czas pracy. I mamy sytuację:

Wybieramy np 1
Teraz musimy mając tylko VehicleId = 1 dowiedzieć się ze VehicleId = 1 jest w oddziale DepartmentId = 4 i na tej podstawie sprawdzić jaki jest CalendarDayId dla tego oddziału i przejść dalej.
Pamiętając że użytkownik może oczywiście wybrać kilka urządzeń np 1, 3, 6

Nadal tłumaczysz to zwile.

Może na przykładzie bedzie prościej, jaki wynik oczekujesz dla tych 3 rekordów?

Zgodnie z tabelą Vehicle:

1 przypadek:
Wybieram 2, czyli VehicleId = 2, sprawdzam i otrzymuje CalendarDayId = 3 i przechodzę dalej.

2 Przypadek:
Wybieram 1, czyli VehicleId = 1, widzę ze mam NULL (czyli go nie mam) wiec sprawdzam jaki jest oddział maszyny 1 z tablicy Vehicel, gdy już wiem ze to 4 sprawdzam i otrzymuje CalendarDayId = 2 i przechodzę dalej

3 Przypadek
Wybieram 3, czyli VehicleId = 3, widzę ze mam NULL dla VehicleId=3, sprawdzam odział dla 3 i jest to DepartmentId = 1, sprawdzam, ale tez mam dla niego NULL, więc sprawdzam dalej jaki jest CompanyId, wiem że już że 1 więc otrzymuje CalendarDayId = 3

Zakładając że dla dnia 01-07-2021 posiadamy tylko te 3 rekordy, wybierając numer urządzenia różny od 1 i 2, otrzymamy wynik CalendarDayId= 3 dla wszystkich pozostałych urządzeń, ponieważ DepartmentId = NULL i VehicleId = NULL i tylko CompanyId mam nie NULL więc sprawdzam po tym i przechodzę dalej

Teraz mam nadzieję że już wszystko będzie jasne(:

Trzymajmy się tych 3 rekordów.

Wybieram 1, czyli VehicleId = 1, widzę ze mam NULL

where vehicleid=1 zwróci pusty zestaw, więc skup sie na przygotowaniu danych

bo na mój gust to kwestia joinów

select 
  c.calendardayid
  c.companyid
  c.departmentid
  COALESCE(c.vehicleid,d.vehicleid,co.vehicleid)
 from 
   calendar c
   left join vehicle d on d.departmentid= = c.departmentid and c.vehicleid is null
   left join vehicle co on co.companyid = c.companyid and c.vehicleid is null and c.departmentid is null
   

Lecz zapytanie zwraca wszystkie możliwości :

a przy wyborze VehicleId = 1 powinno zwrócić tylko jeden rekort:
Dlatego iż w tabeli VehicleId = NULL sprawdził że maszyna 1 jest w oddziale Department = 4 .

i kolejne pytanie gdzie wstawić zapytanie WHERE VehicleId = 1? Ponieważ gdy to zrobię normalnie w zapytaniu czy stworzę podzapytanie, nie wyszuka odpowiedniego VehicleId ponieważ w pierwszym kroku w tabeli mamy NULL dla VehicleId =1 i dlatego sprawdzamy po DepartmentId ewentualnie sprawdzalibyśmy po kolejnym kroku CompanyId.

W naszym przypadku jedynie dla VehicleId = 2 to zadziała poprawnie, ponieważ tylko dla 2 mamy w pierwszym kroku poprawną wartość:

SELECT
	c.Date ,c.calendardayid  ,c.companyid  ,c.departmentid  ,c.VehicleId ,c.IsDeleted 
	,cds.CalendarShiftId
	,cs.AvailableTime
   ,COALESCE(c.vehicleid,d.vehicleid,co.vehicleid) as 'V'
FROM
	[TEST].[dbo].[Calendar] c
	LEFT JOIN [TEST].[dbo].[vehicle] d on d.departmentid = c.departmentid and c.vehicleid is null
	LEFT JOIN [TEST].[dbo].[vehicle] co on co.companyid = c.companyid and c.vehicleid is null and c.departmentid is null
	JOIN  [TEST].[dbo].[CalendarDayShift] cds ON cds.CalendarDayId = c.CalendarDayId
	JOIN [TEST].[dbo].[CalendarShift] cs ON cs.Id = cds.CalendarShiftId
  
   WHERE 
    c.Date BETWEEN '2022-02-01 00:00:00.000' AND '2022-02-01 23:00:00.000' 
	AND c.IsDeleted =0
	AND d.VehicleId =2

a dla reszty nic nie zwóci :(

Not to jest sql, jak szukamy vehicleid=1 to nie mam magi i że coś jest "sprawdzone...

select 
  c.calendardayid
  c.companyid
  c.departmentid
  COALESCE(c.vehicleid,d.vehicleid,co.vehicleid)
 from 
   calendar c
   left join vehicle d on d.departmentid= = c.departmentid and c.vehicleid is null
   left join vehicle co on co.companyid = c.companyid and c.vehicleid is null and c.departmentid is null
where
  COALESCE(c.vehicleid,d.vehicleid,co.vehicleid)=1

Cały czas się uczę sqla lecz z nie każdy problem potrafię rozwiązać sam, i dlatego tutaj pytam.

teraz mamy przesortowane po Vehicle 1 lecz nadal mamy 2 rekordy, a właściwy jest tylko jeden:

jak mam wybrac ten właściwy? a jest nim wiersz 2, gdzie tylko VehicleId = NULL ??

dlaczego tylko ten drugi jest własciwy? Pierwszy też jest ok bo dotyczy innego calendardayid.

W kalendarzu ustawiony jest czas w jakim powinny pracować maszyny dla całej firmy o czym świadczy że CopmantId nie jest NULL, następnie w jednym oddziale (w naszym przypadku departmentId = 4) został ustawiony inny czas (analogicznie DepartmentId różny od NULL), ale dla maszyny VehicleId= 1 nie został ustawiony indywidualny czas (tylko dla VehicleId=2), dlatego VehicleId=1 pracuje według czasu ustawionego dla oddziału.

Hierarchia sprawdzania prawidłowego czasu jest następująca:
1-Najpierw VehicleId (Czas indywidualny dla danej maszyny)
2- DepartmentId (sprawdzamy jeśli VehicleId = NULL, wszystkie maszyny w tym oddziale pracują według tego czasu)
3- CompanyId ( jeśli 1 i 2 są NULL oznacza to że dana maszyna ma pracować zgodnie z czasem ustawionym dla całej firmy)

Wiersz 2 jest dlatego prawidłowy, iż zgodnie z hierarchią sprawdzania dopiero DepartmentId nie jest NULL więc chcemy wyświetlić ten czas (ustawiony dla tego oddziału).
I z tym mam największy problem aby tylko ten rekord wyświetlić.
Mam nadzieję że jasno to tłumaczę.

Opisałeś jeszcze raz jak chcesz namierzyć vehicleid, zupełnie pominąłeś to o co pytałem...

Właśnie o to chodzi by złapać tylko ten 2 wiersz(nie zawsze jest to 2 wiersz), i tylko na jego podstawie odczytać CalendarDayId. Starałem się opisac logikę za pomocą której mamy wiedzieć który CalendarDayId jest prawidłowy.
Pierwszy wiersz odnosi się do całęj firmy dlatego go w naszym przypadku pomijamy
Drugi wiersz odnosi się do oddziału gdzie znajduje się nasza maszyna Vehicleid=1 i tylko jego powinniśmy uwzględnić ponieważ dla vehicle mamy NULL

Dla mnie ciagle nie jest jasne jak to przypisac, dla calendarydayid 3 mamy companyid 1 czyli maszyny od 1 do 10, w drugim rekordzie calendarid jest 2 tu jednak jest zdefiniowany departament czyli maszyny 1 i 2. dlatego dla.mnie vehicleid 1 ma dwa calenadarydayid i oba są poprawne.

Teoretycznie oba calendardayid są poprawne, ale w tym przypadku calendardayid = 2 jest ważniejszy(ponieważ jest bardziej spersonalizowany względem maszyny) i tylko jego chcemy odczytać. Obrazowo można by powiedzieć że widząc te 3 kolumny (companyId, departmentid i vehicleid) ważniejszy jest wiersz, który ma mniej NULL-ów i tylko ten chcemy zobaczyć i na nim się w dalszej części opierać.

To tak nie przejdzie, bo raz jeden rekord jest ważniejszy raz nie.

skoro ten calendarydayid nie ma znaczenia to najprościej tak:

select distinct
  c.data
  COALESCE(c.vehicleid,d.vehicleid,co.vehicleid)
 from 
   calendar c
   left join vehicle d on d.departmentid= = c.departmentid and c.vehicleid is null
   left join vehicle co on co.companyid = c.companyid and c.vehicleid is null and c.departmentid is null
where
  COALESCE(c.vehicleid,d.vehicleid,co.vehicleid)=1