sql server - Inner join or Left outer join based on condition

Question

I am trying to add a condition in my query to do INNER JOIN or LEFT OUTER JOIN

These are the two queries

   USE [tisonline]

   SELECT TOP 1000 *
   FROM Jobs AS j
   LEFT OUTER JOIN JobQueries AS jq ON j.JobID = jq.JobID 
   LEFT OUTER JOIN Agents AS agt ON agt.AgentID = jq.AgentID
   where j.isMigrated = 1 

   SELECT TOP 1000 *
   FROM Jobs AS j
   INNER JOIN JobQueries AS jq ON j.JobID = jq.JobID 
   INNER JOIN Agents AS agt ON agt.AgentID = jq.AgentID
   where j.isMigrated = 0 

Schema of the Following tables:

Job: {
        [JobID]
        ,[JobGUID]
      ,[Duplicate]
      ,[CreateByTisForAgency]
      ,[TisClientCode]
      ,[AgencyID]
      ,[AgencyName]
      ,[BookingAgentID]
      ,[LanguageID]
      ,[ReqGender]
      ,[AnotherGender]
      ,[ProfessionalAccLevelReq]
      ,[InstructionsToInterpreter]
}
  JobQueryTable
  {
   [JobQueryID]
      ,[JobID]
      ,[JobGuid]
      ,[NonEnglishSpeakerName]
      ,[DuplicateJob]
      ,[JobDate]
      ,[JobStartTime]
      ,[JobEndTime]
      ,[JobState]
      ,[JobTier]
      ,[LanguageID]
      ,[AgencyID]
      ,[AgencyName]
      ,[AgentID]
   }
Agent Tabe 
{  
[AgentID]
      ,[AgentGUID]
      ,[Position]
      ,[Section]
      ,[Role]
      ,[AgentDetails_PersonalDetailsID]
      ,[Agency_AgencyID]
      ,[RecieveEmailUpdates]
      ,[ParticipateInTisSurvey]
      ,[RecieveSMSUpdates]
}

Job table and job query table has one to one relation ship based on Job ID. Also Job query table has one to one relation with agent based on agent ID. But for isMigrated true in jobs table the agent Id would always be NULL.

I want to combine those two queries to do INNER JOIN or LEFT OUTER JOIN based on the ismigrated value. As migrated jobs won't have any agentID in job queries table.

Let me know if further details are required.

Answer 1

Always do the LEFT OUTER JOIN and add the WHERE condition to simulate INNER JOIN functionality for isMigrated = 0.

SELECT TOP 1000 *
FROM [tisonline].[dbo].[Jobs] AS j
LEFT OUTER JOIN [tisonline].dbo.JobQueries AS jq 
ON j.JobID = jq.JobID 
LEFT OUTER JOIN [tisonline]. dbo.Agents AS agt ON agt.AgentID = jq.AgentID
WHERE j.isMigrated = 1 
    OR agt.AgentID IS NOT NULL