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.
See Question&Answers more detail:
os