title: Comparing row values from same table
body: <p>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.</p>
  
  <p>Data:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Expected Result:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have been trying this script but it bring results from other CUSTFIELDNAME too:</p>
  
  <pre><code>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 &lt;&gt; CWB.CUSTFIELDID 
      where  CWA.tablename = 'EMPLOYEE' and CWB.TABLENAME ='EMPLOYEE' and (CWA.CUSTFIELDVALUE = 'YES' and CWB.CUSTFIELDVALUE = 'YES')
  </code></pre>