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

Microsoft SQL Server 2008 Does not connect
Specifying login creditials for remote SQL connection string in ASP.Net/Entity Framework Core
SQL sys.dm_exec_query_stats Last_Elapsed_Time nonsense
How can I update duplicate rows in SQL server but leave the original?
Automates sending email using SQL Server 2008 T-SQL, with custom message title and body
I want a script to decrypt the encrypted stored procedure
R and SQL Connection
Db first approach, composite primary key in table, EF not creating entity for that table
SQL query multiply decimal
How to increment multiple variables in a loop?`
SSRS: Raising records up to a higher group level
Select columns with count of related items from tables in ms sql server
SSIS Package Error - BULK INSERT “file does not exist”
Automatically enable/disable SQL Agent Job
MS SQL Check for duplicate in two fields
Change composite Primary Key effect on update performance

Categories

HOME
xbox-live
visual-studio-2015
ibm-watson-cognitive
filterrific
drupal-7
performancecounter
boost-thread
vs-team-services
puppet
wxwidgets
decorator
is-empty
adsense
user
uiactivityviewcontroller
finite-group-theory
data-synchronization
spring-cloud-config
opera-mini
linq-to-sql
attask
kadanes-algorithm
aws-cognito
sql-update
autolayout
onesignal
emv
cep
widevine
edge-detection
positioning
postgresql-9.2
fractals
pljson
rhino
powershell-remoting
remote-server
shibboleth
broadleaf-commerce
autocompletetextview
zero
parse-android-sdk
deepstream.io
twiki
html-agility-pack
resuming-training
python-idle
windows-firewall
sapui
webdatagrid
bnd
fedora20
instruction-set
iphone-developer-program
settimeout
alter
software-product-lines
multilingual-app-toolkit
emokit
dac
sun-codemodel
handlebars.java
cmocka
android-viewholder
datasnap
p-np
abas
hg-git
magento-1.12
start-job
mser
gfs
ng-pattern
magickwand
wpdb
tridion2009
javascriptserializer
datarepeater
ninject-extensions
hibernate3
supersized
point-sprites
flexicious
ou
cookieless
sendfile
chatroom
osx-leopard
delegatecommand
kpi

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