时间戳

首页 > 解决方案 > Comparing row values from same table

sql - Comparing row values from same table

问题描述

I am trying to generate a query. I need the values from column TABLEOBJECTID from the below table where TABLENAME = 'EMPLOYEE' and INTERFACE MAINTAINED (CUSTFIELDNAME) = YES (CUSTFIELDVALUE) and TRANSFER FLAG (CUSTFIELDNAME) = YES (CUSTFIELDVALUE) Any help is appreciated. The SQL shown below isnt working and gets data but it gets values from other fields as well.

Data:

TABLEOBJECTID CUSTFIELDID TABLENAME CUSTFIELDNAME           CUSFIELDVALUE
2                    4    EMPLOYEE  INTERFACE MAINTAINED    No
2                    5    EMPLOYEE  TRANSFER HOURS TO AG    Yes
2                    7    EMPLOYEE  TRANSFER FLAG           Yes
3                    4    EMPLOYEE  INTERFACE MAINTAINED    Yes
3                    7    EMPLOYEE  TRANSFER FLAG           Yes
31                   4    EMPLOYEE  INTERFACE MAINTAINED    No
31                   5    EMPLOYEE  TRANSFER HOURS TO AG    No
31                   6    EMPLOYEE  DEFAULT JOB CODE    
94                   4    EMPLOYEE  INTERFACE MAINTAINED    No
94                   6    EMPLOYEE  DEFAULT JOB CODE        0325
94                   7    EMPLOYEE  TRANSFER FLAG           No
96                   4    EMPLOYEE  INTERFACE MAINTAINED    No
96                   6    EMPLOYEE  DEFAULT JOB CODE        0325
100                  4    EMPLOYEE  INTERFACE MAINTAINED    No
100                  5    EMPLOYEE  TRANSFER HOURS TO AG    No
100                  6    EMPLOYEE  DEFAULT JOB CODE    
100                  7    EMPLOYEE  TRANSFER FLAG           No
101                  4    EMPLOYEE  INTERFACE MAINTAINED    Yes
101                  6    EMPLOYEE  DEFAULT CODE            9760
101                  7    EMPLOYEE  TRANSFER FLAG           Yes

Expected Result:

TABLEOBJECTID CUSTFIELDID TABLENAME CUSTFIELDNAME           CUSFIELDVALUE
3                    4    EMPLOYEE  INTERFACE MAINTAINED    Yes
3                    7    EMPLOYEE  TRANSFER FLAG           Yes
101                  4    EMPLOYEE  INTERFACE MAINTAINED    Yes   
101                  7    EMPLOYEE  TRANSFER FLAG           Yes

I have been trying this script but it bring results from other CUSTFIELDNAME too:

select CWA.TABLEOBJECTID ,CWA.CUSTFIELDID ,CWA.CUSTFIELDNAME ,CWA.CUSTFIELDVALUE ,CWA.TABLENAME ,CWA.TABLEOBJECTID  from az.CWCUSTFIELD CWA 
    inner join  az.CWCUSTFIELD CWB on CWA.TABLEOBJECTID  =CWB.TABLEOBJECTID  and CWA.CUSTFIELDID <> CWB.CUSTFIELDID 
    where  CWA.tablename = 'EMPLOYEE' and CWB.TABLENAME ='EMPLOYEE' and (CWA.CUSTFIELDVALUE = 'YES' and CWB.CUSTFIELDVALUE = 'YES')

标签： sqlsql-serverdatabasesyntax

解决方案

您错过了 CUSTFIELDID 上的过滤器，因为您只查找 INTERFACE MAINTAINED (4) 和 TRANSFER FLAG(7)，因此您应该在此字段上为两个表添加过滤器。

AND CWA.CUSTFIELDID(4,7) AND CWB.CUSTFIELDID(4,7)

或者您可以在 CUSTFIELDNAME 而不是 CUSTFIELDID 上应用过滤器，例如

AND CWA.CUSTFIELDNAME IN ('INTERFACE MAINTAINED', 'TRANSFER FLAG')
   AND CWB.CUSTFIELDNAME IN ('INTERFACE MAINTAINED', 'TRANSFER FLAG')

最后的查询 -

select CWA.TABLEOBJECTID ,
  CWA.CUSTFIELDID ,
  CWA.CUSTFIELDNAME ,
  CWA.CUSTFIELDVALUE ,
  CWA.TABLENAME ,
  CWA.TABLEOBJECTID  
from az.CWCUSTFIELD CWA 
inner join  az.CWCUSTFIELD CWB on CWA.TABLEOBJECTID  =CWB.TABLEOBJECTID  
            and CWA.CUSTFIELDID <> CWB.CUSTFIELDID 
where  CWA.tablename = 'EMPLOYEE' 
  and CWB.TABLENAME ='EMPLOYEE' 
  and CWA.CUSTFIELDVALUE = 'YES' 
  and CWB.CUSTFIELDVALUE = 'YES'
  and CWA.CUSTFIELDNAME IN ('INTERFACE MAINTAINED', 'TRANSFER FLAG')
  and CWB.CUSTFIELDNAME IN ('INTERFACE MAINTAINED', 'TRANSFER FLAG')

推荐阅读