基于条件的内部联接或左外部联接 [英] Inner join or Left outer join based on condition

问题描述

我正在尝试在查询中添加条件以进行INNER JOIN或LEFT OUTER JOIN

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

这是两个查询

   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 

下表的模式:

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]
}

作业表和作业查询表具有基于作业ID的一对一关系.作业查询表也基于代理ID与代理具有一对一的关系.但是对于作业表中的isMigrated true，代理ID始终为NULL.

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.

我想结合这两个查询来基于被迁移的值进行INNER JOIN或LEFT OUTER JOIN.由于迁移的作业在作业查询表中将没有任何agentID.

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.

推荐答案

始终执行LEFT OUTER JOIN并添加WHERE条件以模拟isMigrated = 0的INNER JOIN功能.

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

这篇关于基于条件的内部联接或左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！