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

SQL Server database has 2 log files and I want to remove one. HOW?
Programming Entity Framework Example Not Returning Identity
Moving stored procedure from Firebird to SQL Server [closed]
VBA MS Access error 30013
VB.Net data source connection error
LINQ to SQL anomaly
Do I need to uninstall vwd 2010 express before installing vs 2010 professional?
Access Recordsets and SQL Server with Pass Through Queries - If I update a field in the recordset, does it update the server?
Insert Sub Query inside view
SQLException Foreign Key Error Getting Child Table
Connecting to local SQLserver from a local Windows managed service
What is difference between operater != and <> in SQL Server? [duplicate]
SQL Server Replication - Is it possible between two different database models?
Database Tuning Advisor: Naming of Recommended Indexes and Statistics
prefix field with letters
Can i user a where statement by first column? [duplicate]

Categories

HOME
laravel-5
automated-tests
azureservicebus
sequelize.js
jpanel
data-science-experience
packer
cartodb
squarespace
reduction
aptana
anychart
boolean-expression
ng2-dragula
url.action
avplayeritem
nmf
cronet
printdocument
data-cleansing
upnp
owl-api
mayavi
dhtmlx-scheduler
jboss5.x
crt
helix-3d-toolkit
data-extraction
powershell-remoting
autocompletetextview
parse-android-sdk
meta-raspberrypi
stereo-3d
tableau-server
chunked-encoding
rhel6
struts-layout
google-maps-ios
jexl
photon-controller
addin-express
date-range
lotus
restivejs
slam-algorithm
zurb-foundation-apps
dbscan
axes
self-hosting
direct3d12
ninject.web.mvc
selendroid
subresource-integrity
abas
risk-analysis
manjaro
divide-by-zero
swift2.1
jazz
magento-1.12
inputaccessoryview
libsndfile
kendo-mvvm
microblaze
cg
korma
kraken.js
javascriptmvc
dynamic-binding
multiple-login
stagefright
wpdb
maven-ear-plugin
autostart
javascriptserializer
getusermedia
caliper
serp
executable-format
gtk2hs
symbol-server
post-redirect-get
clause
file-encodings
webrat
method-signature
data-execution-prevention

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