清除重复项并比较计数与字段值T-SQL

I'm working with Two tables and I've joined them together. I'm almost done with the query, but I'm not sure how to proceed further.

我正在使用两张桌子,我已将它们加在一起。我差不多完成了查询,但我不确定如何继续进行。

There are two tables:

有两个表:

Person - Table that contains records of people participating in the Pen Pal program

人员 - 包含参与Pen Pal计划的人员记录的表格

Contact - Table that contains contact records.

联系人 - 包含联系人记录的表。

The Person table has a type field that determines if they're class 1 or class 2 (noted by "O" or "I")

Person表有一个类型字段,用于确定它们是1类还是2类(用“O”或“I”表示)

So Far the query is this:

到目前为止,查询是这样的:

select p.PersonId as PersonTableID,
       c.InmateId as ContactTableId,
       p.PersonName,
       p.Country,
       p.MaxContactNum as MaxContacts,
       p.Type as PersonType,
       c.CorrespId,
       c.LastUpdateDate,
       p.language,
       (select count(InmateId)
       from tblcfContacts
       where InmateId=p.PersonId) as ContactCount,
       case
        when (select count(InmateId)
                from tblcfContacts
                where InmateId=p.PersonId or CorrespId=p.PersonId) > p.MaxContactNum
            then 'Too Many'
        when (select count(InmateId)
                from tblcfContacts
                where InmateId=p.PersonId or CorrespId=p.PersonId) = p.MaxContactNum
            then 'Just Right'
        when (select count(InmateId)
                from tblcfContacts
                where InmateId=p.PersonId or CorrespId=p.PersonId) < p.MaxContactNum
            then 'Room For More'
       end as ContactStatus
    from tblCfPerson as p 
    inner join tblCfContacts as c 
    on p.PersonId=c.InmateId or p.PersonId=c.CorrespId where p.Country='CA'

The Query is returning results like this:

Query返回如下结果:

As you can see, there are duplicates, and the comparison between the Maximum Contact Field and Assigned Contacts is a bit off.

如您所见,有重复项,最大联系人字段和分配联系人之间的比较有点偏。

How can I only see one row per person and fix the Contact Count Calculation?

我怎样才能看到每人一行并修复联系计数计算?

Thanks in advance!

提前致谢!

2 个解决方案

#1

If you don't want to show the columns from the tblCfContacts table, you should remove the JOIN with this table. Also, using a CROSS APPLY would help to write a shorter query:

如果您不想显示tblCfContacts表中的列,则应使用此表删除JOIN。此外,使用CROSS APPLY将有助于编写更短的查询:

select p.PersonId as PersonTableID,
       p.PersonName,
       p.Country,
       p.MaxContactNum as MaxContacts,
       p.Type as PersonType,
       p.language,
       x.ContactCount,
       case
        when x.ContactCount > p.MaxContactNum then 'Too Many'
        when x.ContactCount = p.MaxContactNum then 'Just Right'
        when x.ContactCount < p.MaxContactNum then 'Room For More'
       end as ContactStatus
from tblCfPerson as p 
CROSS APPLY (
    select count(InmateId) AS ContactCount
    from tblcfContacts c
    where c.InmateId=p.PersonId or c.CorrespId=p.PersonId)
) x
where p.Country='CA'

#2

The ContactTableId is creating unique rows for every PersonTableID. If the field "ContactTableId" is not necessary to the output of the report, remove it and you'll remove the duplicate PersonTableID rows. However, it seems useful information to note that a PersonTableID can have more than one ContactTableId.

ContactTableId为每个PersonTableID创建唯一的行。如果报告输出中不需要“ContactTableId”字段,请将其删除,然后删除重复的PersonTableID行。但是,注意PersonTableID可以有多个ContactTableId似乎是有用的信息。

Or to put it plainly (if I'm inferring the context correctly) each Person is writing to multiple Inmates.

或者说清楚(如果我正确推断上下文)每个人都在写给多个囚犯。

#1