Help with sql join

I have a sql statement that depending on whether or not a certain condition exists I need to join to a table with a specific value ELSE join with a different value.  Sample below.  Im not sure if I just need to set a boolean based on certain conditions.  I have coded it that way below pay attention to the CASE and also the IF below.  Not even sure if this possible but would like to accomplish this in such a way.  Thanks.

SELECT distinct
		AV.DriverCode AS DriverCode
		,RTRIM(RM.PreferredName) + ' ' + RTRIM(RM.lastname) AS DriverName			
		,FM.PhoneExtension AS FMExtension
		,OS.Extension as FMOverflowGroup	
		,P.Extension as PayrollExtension, 
CASE 
WHEN DF.FleetTrainingManager = '' THEN Set BooleanValueA = True --Set a boolean value to use for conditional join
WHEN DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted = '' THEN Set BooleanValueA = True  --Set a boolean value to use for conditional join
WHEN DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted <> '' THEN Set BooleanValueB = True --Set a boolean value to use for conditional join
END AS FleetManager
			
	FROM rtidriver.dbo.tbDMDriverFile DF 
		JOIN rtidriver.dbo.tbDMAvailable AV ON AV.DriverId = DF.DriverId
		If BooleanValueA
		JOIN rticompany.dbo.tbCompanyUserMaster FM ON FM.UserName =   rtrim(DF.FleetTrainingManager)
		
		ELSE
		JOIN rticompany.dbo.tbCompanyUserMaster FM ON FM.UserName =   rtrim(DF.CurrentSupervisorCode)

		JOIn rticompany.dbo.tbCMPRSCResourceMaster RM on RM.Id = DF.driverid	
		JOIN rtidriver.dbo.Payroll_Index P ON (P.FromCode < AV.DriverCode) AND (P.ToCode >= AV.DriverCode)
		left join Company.tbOverflowGroupDetail OD on OD.UserId = FM.UserName
		left join Company.tbOverflowGroupSummary OS on OS.Id = OD.OverflowGroupSummaryId

Open in new window

Steve HougomDeveloper IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Instead of a conditional join just join to each of them and have a conditional select.

SELECT distinct
	AV.DriverCode AS DriverCode,
	RTRIM(RM.PreferredName) + ' ' + RTRIM(RM.lastname) AS DriverName,
	CASE
		WHEN DF.FleetTrainingManager = '' OR (DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted = '' THEN FM_Mgr.PhoneExtension
		ELSE FM_Sup.PhoneExtension
	END AS FMExtension,
	OS.Extension as FMOverflowGroup,
	P.Extension as PayrollExtension		
FROM rtidriver.dbo.tbDMDriverFile AS DF 
JOIN rtidriver.dbo.tbDMAvailable AS AV
	ON AV.DriverId = DF.DriverId
LEFT OUTER JOIN rticompany.dbo.tbCompanyUserMaster AS FM_Mgr
	ON FM_Mgr.UserName = rtrim(DF.FleetTrainingManager)
LEFT OUTER JOIN rticompany.dbo.tbCompanyUserMaster AS FM_Sup
	ON FM_Sup.UserName = rtrim(DF.CurrentSupervisorCode)
INNER JOIN rticompany.dbo.tbCMPRSCResourceMaster RM
	ON RM.Id = DF.driverid	
INNER JOIN rtidriver.dbo.Payroll_Index P
	ON (P.FromCode < AV.DriverCode) AND (P.ToCode >= AV.DriverCode)
LEFT OUTER JOIN Company.tbOverflowGroupDetail OD
	on OD.UserId = FM.UserName
LEFT OUTER JOIN Company.tbOverflowGroupSummary OS
	on OS.Id = OD.OverflowGroupSummaryId

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
have you tried:
JOIN rticompany.dbo.tbCompanyUserMaster FM ON FM.UserName = case when BooleanValueA then  rtrim(DF.FleetTrainingManager) else rtrim(DF.CurrentSupervisorCode)
 end
0
Steve HougomDeveloper IIAuthor Commented:

have you tried:
JOIN rticompany.dbo.tbCompanyUserMaster FM ON FM.UserName = case when BooleanValueA then  rtrim(DF.FleetTrainingManager) else rtrim(DF.CurrentSupervisorCode)
 end

emoreau,

No I have not tried that but might.  I would try that but Im not sure how to declare and set the Boolean value in SQL syntax.

e.g.

var boolvalue

case when something = somevalue set boolvalue = true;
 
Im not sure how especially within the context of my original query.

BriCrowe,

Ive tried your example and its close but not quite.  Apparently im not getting the right results.

Here is what I have currently.  Apparently its Returning NULLS in some of the results of the FMPhoneTeam and FMBackupPhoneTeam columns.  Havent figured out why that is just yet.  The FMExtension and FMUserId columns seem to be correct though.

SELECT DISTINCT 
                         AV.DriverCode, RTRIM(RM.PreferredName) + ' ' + RTRIM(RM.LastName) AS DriverName, 
						 CASE WHEN DF.FleetTrainingManager <> '' OR (DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted = '') THEN FM_Mgr.PhoneExtension 
							ELSE FM_Sup.PhoneExtension 
						 END AS FMExtension, 
                         CASE WHEN DF.FleetTrainingManager <> '' OR (DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted = '') THEN RTRIM(REPLACE(FM_Mgr.UserName, 'mysite.com\', '')) 
							ELSE RTRIM(REPLACE(FM_Sup.UserName, 'mysite.com\', ''))
						 END AS FMUserId, 
						 CASE WHEN DF.FleetTrainingManager <> '' OR (DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted = '') THEN PTS_FMgr.Extension 
							ELSE PTS_Sup.Extension 
						 END AS FMPhoneTeam, 
                         CASE WHEN DF.FleetTrainingManager <> '' OR (DF.FleetTrainingManager <> '' AND DF.DateTrainingCompleted = '') THEN PTS_FMgr.Extension 
							ELSE PTS_Sup.Extension 
						 END AS FMBackupPhoneTeam, 
                         P.Extension AS PayrollExtension, 
						 DF.VoiceMailNumber, 
						 UM.UnitNumber
						FROM            rtiDriver.dbo.tbDMDriverFile AS DF INNER JOIN
                         rtiDriver.dbo.tbDMAvailable AS AV ON AV.DriverId = DF.DriverId 
						 LEFT OUTER JOIN
                         rtiCompany.dbo.tbCompanyUserMaster AS FM_Mgr ON FM_Mgr.UserName = 'mysite.com\' + RTRIM(DF.FleetTrainingManager) 
						 LEFT OUTER JOIN
                         rtiCompany.dbo.tbCompanyUserMaster AS FM_Sup ON FM_Sup.UserName = 'mysite.com\' + RTRIM(DF.CurrentSupervisorCode) 
						 INNER JOIN
                         rtiCompany.dbo.tbCMPRSCResourceMaster AS RM ON RM.Id = DF.DriverId 
						 INNER JOIN
                         rtiDriver.dbo.Payroll_Index AS P ON P.FromCode < AV.DriverCode AND P.ToCode >= AV.DriverCode 
						 LEFT OUTER JOIN
                         rtiEquipment.dbo.tbEQUnitMaster AS UM ON UM.Driver1Code = AV.DriverCode OR UM.Driver2Code = AV.DriverCode 
						 LEFT OUTER JOIN
                         Company.tbPhoneTeamDetail AS PTD ON PTD.UserId = FM_Sup.UserName 
						 LEFT OUTER JOIN
                         Company.tbPhoneTeamSummary AS PTS_FMgr ON PTS_FMgr.Id = PTD.PhoneTeamSummaryId
						 LEFT OUTER JOIN
                         Company.tbPhoneTeamSummary AS PTS_Sup ON PTS_Sup.Id = PTD.PhoneTeamSummaryId 
						 LEFT OUTER JOIN
                         Company.tbPhoneTeamSummary AS PTSB_FMgr ON PTSB_FMgr.Id = PTS_FMgr.BackupPhoneTeamSummaryId 
						 LEFT OUTER JOIN
                         Company.tbPhoneTeamSummary AS PTSB_Sup ON PTSB_Sup.Id = PTS_Sup.BackupPhoneTeamSummaryId
WHERE        (DF.VoiceMailNumber <> '')

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Steve HougomDeveloper IIAuthor Commented:
Before I forget.  Here is the original query:

SELECT DISTINCT 
                         AV.DriverCode, RTRIM(RM.PreferredName) + ' ' + RTRIM(RM.LastName) AS DriverName, FM.PhoneExtension AS FMExtension, RTRIM(REPLACE(FM.UserName, 
                         'mysite.com\', '')) AS FMUserId, PTS.Extension AS FMPhoneTeam, PTSB.Extension AS FMBackupPhoneTeam, P.Extension AS PayrollExtension, DF.VoiceMailNumber, 
                         UM.UnitNumber
FROM            rtiDriver.dbo.tbDMDriverFile AS DF INNER JOIN
                         rtiDriver.dbo.tbDMAvailable AS AV ON AV.DriverId = DF.DriverId INNER JOIN
                         rtiCompany.dbo.tbCompanyUserMaster AS FM ON FM.UserName = 'mysite.com\' + RTRIM(DF.CurrentSupervisorCode) INNER JOIN
                         rtiCompany.dbo.tbCMPRSCResourceMaster AS RM ON RM.Id = DF.DriverId INNER JOIN
                         rtiDriver.dbo.Payroll_Index AS P ON P.FromCode < AV.DriverCode AND P.ToCode >= AV.DriverCode LEFT OUTER JOIN
                         rtiEquipment.dbo.tbEQUnitMaster AS UM ON UM.Driver1Code = AV.DriverCode OR UM.Driver2Code = AV.DriverCode LEFT OUTER JOIN
                         Company.tbPhoneTeamDetail AS PTD ON PTD.UserId = FM.UserName LEFT OUTER JOIN
                         Company.tbPhoneTeamSummary AS PTS ON PTS.Id = PTD.PhoneTeamSummaryId LEFT OUTER JOIN
                         Company.tbPhoneTeamSummary AS PTSB ON PTSB.Id = PTS.BackupPhoneTeamSummaryId
WHERE        (DF.VoiceMailNumber <> '')

Open in new window


The goal is to select a FleetTrainingManager vs a DriverSupervisor depending on certain conditions.  Some of those values include UserId and Phone Extension type data.
0
Brian CroweDatabase AdministratorCommented:
I think we just needed to extend the query i provided to include the tbPhoneTeamSummary table.


LEFT OUTER JOIN Company.tbOverflowGroupDetail OD
      on OD.UserId = ISNULL(FM_Mgr.UserName, FM_Sup.UserName)
0
Steve HougomDeveloper IIAuthor Commented:
Bri,

I found what it was.  I was missing a couple joins.  Based on your model.  I had left out this:

 Company.tbPhoneTeamDetail AS PTD_Mgr ON PTD_Mgr.UserId = FM_Mgr.UserName LEFT OUTER JOIN
 Company.tbPhoneTeamDetail AS PTD_Sup ON PTD_Sup.UserId = FM_Sup.UserName LEFT OUTER JOIN

Open in new window


As compared to what I have above this:

LEFT OUTER JOIN
                         Company.tbPhoneTeamDetail AS PTD ON PTD.UserId = FM_Sup.UserName 

Open in new window


It was pulling the driver supervisor Phone info every time!  My bad.

Its working great sir and your I thank you a million!!  Ive never done anything like this before.  I will also consider your latest suggestion to enhance it but Im closing this one out and giving you A's.  Thanks again bri!
0
Steve HougomDeveloper IIAuthor Commented:
BriCrowe nails it!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.