SQL modification to include agents with no team assignment

[harresh123]

I am using the below query to get me the agent data along with skill-groups, team assignment , Peripheral Number, is supervisor or not etc but this doesn't include agents with no team assigned. How can I modify this to include agents with no team assigned to be included.

SELECT P.FirstName,
P.LastName,
P.LoginName,
A.PeripheralNumber,
A.SupervisorAgent,
SG.PeripheralName AS SkillGroup,
AT.EnterpriseName AS AgentTeam
FROM Agent A,
Person P,
Skill_Group SG,
Skill_Group_Member SGM,
Agent_Team_Member ATM,
Agent_Team AT
WHERE A.PersonID = P.PersonID
AND (A.Deleted = 'N')
AND A.SkillTargetID = SGM.AgentSkillTargetID
AND SGM.SkillGroupSkillTargetID = SG.SkillTargetID
AND A.SkillTargetID = ATM.SkillTargetID
AND ATM.AgentTeamID = AT.AgentTeamID
ORDER BY P.LastName

[TalkingScientist]

Here you go. You need to use LEFT join to include the null items. You are doing something called a natural join, that where you do the field = field in the WHERE clause. This should give you what you want

SELECT

       P.firstname,
       P.lastname,
       P.loginname,
       A.peripheralnumber,
       A.supervisoragent,
       SG.peripheralname AS SkillGroup,
       AT.enterprisename AS AgentTeam
FROM

       person P
       LEFT JOIN agent A ON P.personid = A.personid
       LEFT JOIN skill_group_member SGM ON A.skilltargetid = SGM.skillgroupskilltargetid
       LEFT JOIN skill_group SG ON SGM.skillgroupskilltargetid = SG.skilltargetid
       LEFT JOIN agent_team_member ATM ON A.skilltargetid = ATM.skilltargetid
       LEFT JOIN agent_team AT ON ATM.agentteamid = AT.agentteamid
WHERE

       A.personid = P.personid
       AND ( A.deleted = 'N' )
ORDER BY

       P.lastname 

[harresh123]

The SkillGroup is also showing up as NULL for everybody in the above query.

[Piyush Verma · Accepted solution]

You can give this one a try

select p.FirstName,p.LastName,p.LoginName,a.PeripheralNumber,a.SupervisorAgent,sg.PeripheralName AS SkillGroup,at.EnterpriseName AS AgentTeam from Agent a
inner join Person p on p.PersonID = a.PersonID
LEFT join Skill_Group_Member sgm on sgm.AgentSkillTargetID = a.SkillTargetID
LEFT join Skill_Group sg on sg.SkillTargetID = sgm.SkillGroupSkillTargetID
LEFT join Agent_Team_Member atm on atm.SkillTargetID = a.SkillTargetID
LEFT join Agent_Team at on at.AgentTeamID = atm.AgentTeamID
where a.Deleted = 'N'
order by 1,2

[harresh123]

Thanks. The inner join on person ID was the missing link.