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

Reasonable practice for existing db with 75 tables
case statement creating X field and where clause X field in sql server 2008
How clustered indexing is implemented in SQL Server
Best Practices working with Datetimeoffset
DATEDIFF Incorrect results
Not allowing to execute the Stored procedure
Does SQL Server supports BNF notation? [closed]
SQL server connection not working
join two columns to one column if they are numeric in SQL 2008
T-SQL: pass an array of ids to a stored procedures
Using Chrome browser - Pop up menu is not displayed when clicking down arrow of SSRS Report Manager report item in Home
cannot connect to database after putting tasks in sequence container
Accessing Database Objects Using HTTP
Incorrect syntax near the keyword 'set' and 'as' [closed]
How to reorder rows using SQL (not swap)
How to migrate sql server compact edition 4.0 database to sql server

Categories

HOME
cluster-computing
semantic-ui
uibutton
angular2-routing
thunderbird-addon
mapbox-gl-js
command
checksum
gspread
tizen-wearable-sdk
xcode8.3
deeplearning4j
dropbear
click
flexbox
x11
gatsby
spinnaker
xlsx
local
simple-injector
postmessage
ejs
spring-cloud-config
yeoman-generator
ejbca
kadanes-algorithm
jni
flexboxgrid
statusbar
restfb
npm-install
yosys
scalajs-react
codelite
data-conversion
forum
sharp
jackson-dataformat-csv
getjson
pljson
mapguide
swagger-editor
socketpair
issue-tracking
declare
mongoexport
rule
cgo
nashorn
control-flow-graph
constants
g1gc
php-ews
data-integration
azureportal
cgcontext
decompress
crystal-reports-8.5
mbaas
tofixed
emgu
master
debugdiag
update-site
children
django-filer
gradle-eclipse
mongo-c-driver
selecteditem
inet
flexjson
xmp
cmocka
intellitest
textkit
twython
leadtools-sdk
isml
searchkick
c++-actor-framework
candidate-key
tcpreplay
and-operator
ogr2ogr
snmp4j
docopt
genetic-programming
method-overriding
getimagedata
aquafold
coin-flipping
zipalign
xenocode
operations
windows-mobile-6
driver-signing
image-zoom
nscolor
vmware-server
sharpmap
zend-lucene
qt4.6
fluidsynth
phpsh
dynamic-data
mvs
socketstream
n-layer
symfony-2.0
deploying
plone-funnelweb
z-machine
supersized
.app
multibyte-functions
web-analytics-tools
ajaxpro

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