sql-server


SQL join conditional either or not both?


I have 3 tables that I'm joining and 2 variables that I'm using in one of the joins.
What I'm trying to do is figure out how to join based on either of the statements but not both.
Here's the current query:
SELECT DISTINCT
WR.Id,
CAL.Id as 'CalendarId',
T.[First Of Month],
T.[Last of Month],
WR.Supervisor,
WR.cd_Manager as [Manager], --Added to search by the Manager--
WR.[Shift] as 'ShiftId'
INTO #Workers
FROM #T T
--Calendar
RIGHT JOIN [dbo].[Calendar] CAL
ON CAL.StartDate <= T.[Last of Month]
AND CAL.EndDate >= T.[First of Month]
--Workers
--This is the problem join
RIGHT JOIN [dbo].[Worker_Filtered]WR
ON WR.Supervisor IN (SELECT Id FROM [dbo].[User] WHERE FullName IN(#Supervisors))
or (WR.Supervisor IN (SELECT Id FROM [dbo].[User] WHERE FullName IN(#Supervisors))
AND WR.cd_Manager IN(SELECT Id FROM [dbo].[User] WHERE FullNameIN(#Manager))) --Added to search by the Manager--
AND WR.[Type] = '333E7907-EB80-4021-8CDB-5380F0EC89FF' --internal
WHERE CAL.Id = WR.Calendar
AND WR.[Shift] IS NOT NULL
What I want to do is either have the result based on the Worker_Filtered table matching the #Supervisor or (but not both) have it matching both the #Supervisor and #Manager.
The way it is now if it matches either condition it will be returned. This should be limiting the returned results to Workers that have both the Supervisor and Manager which would be a smaller data set than if they only match the Supervisor.
UPDATE
The query that I have above is part of a greater whole that pulls data for a supervisor's workers.
I want to also limit it to managers that are under a particular supervisor.
For example, if #Supervisor = John Doe and #Manager = Jane Doe and John has 9 workers 8 of which are under Jane's management then I would expect the end result to show that there are only 8 workers for each month. With the current query, it is still showing all 9 for each month.
If I change part of the RIGHT JOIN to:
WR.Supervisor IN (SELECT Id FROM [dbo].[User] WHERE FullName IN (#Supervisors))
AND WR.cd_Manager IN(SELECT Id FROM [dbo].[User] WHERE FullName IN(#Manager))
Then it just returns 12 rows of NULL.

Related Links

Fine tune a query?
Binding parameters to stored procedure
Sql Server Discovery report showing features but blank instance names
What is a recommended schema / database design to store custom report settings in my sql database?
Simple SQL - using format inside openquery
Are Select Queries Within Rollback statements Stored On The Log?
Execute sql stored procedure with array input from kettle table input step
SQL - Group Results on Same Row
Inner Join Not Showing All Results
How connections to SQL Server 2012 are managed in ASP.NET MVC4 applications
Extract string between chars in SQL Server strings
how to create Filtered index in stored procedure (SQL Server)
Using Pivoting in SQL Server (Error) - Invalid column name
SQL Server Like Match and Exclude
SQL Server Trigger error for compiling
Mark matches within 7 days of each other - not in 7 day period

Categories

HOME
visual-studio-2015
batch-processing
jar
winapi
formal-verification
parse-server
is-empty
pygame
currency
keystore
cartodb
cpanel
scala-native
titan
pyephem
vs2017
sonarqube-msbuild-runner
ghost-inspector
nsmenuitem
restfb
badge
katharsis
guile
ab-initio
system.data.sqlite
jboss5.x
backup-strategies
idl
fabric-digits
passenger
bing-maps-api
issue-tracking
ddms
web-development-server
knockout-3.0
microdata
worker
startapp
double-buffering
mouseclick-event
azureportal
text-classification
libtiff.net
spring-ioc
xib
tuxedo
gemini
license-key
throughput
rx-groovy
gulp-livereload
google-hadoop
hiera
jai
viewflipper
rhel5
mov
csplit
bsp
sgml
visualstatemanager
flash-cc
winrt-httpclient
bignum
nscolor
backbone-relational
iconv
fileconveyor
client-library
template-haskell
gdt
gjs
goliath
aio
chdatastructures

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App