5 Replies Latest reply on Jan 24, 2014 8:45 AM by BKBK

    MySQL query or CF code to “Flag” a group of query results based on a common value?

    jlig Level 1

      I have the following query that looks up customer accounts:

      SELECT    ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE    
      FROM   Accounts   
      WHERE  ACCOUNT_DESC LIKE '%Eye%'   
      GROUP BY  ACCOUNT_ID

      Which gives me the following result..

       1. ACCOUNT_ID,  SUBSCRIBER_ID, PKG_CODE  
      2. ---------- -------------- -------   
      3. 11016869,    10016598,      N 
      4. 11015922,    10015713,      N 
      5. 11015062,    10014878,      N 
      6. 11018312,    10017973,      Y 
      7. 11018310,    10017973,      N 
      8. 11018309,    10017973,      N 
      9. 11018308,    10017973,      N

      Question: How do I display a "1" in a Flag column if "any one" of the "SUBSCRIBER_ID's" have a value of "Y" in the PKG_CODE column?

      Here are the query results that I'm trying to get to for this example (notice that the last 4 records need to be flagged a "1" because one of them had a "Y" for PKG_CODE and matching SUBSCRIBER_ID's..

      1. ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE, FLAG
      2. ---------- -------------- ------- -----
      3. 11016869,10016598,N
      4. 11015922,10015713,N
      5. 11015062,10014878,N
      6. 11018312,10017973,Y,1
      7. 11018310,10017973,N,1
      8. 11018309,10017973,N,1
      9. 11018308,10017973,N,1

      I tried the following, but it only flags the "Y" records? I need it to also check the SUBSCRIBER_ID" column value..

      SELECT    ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE  
      , IF(PKG_CODE = 'Y', "Y", "N") AS FLAG   
      FROM   Accounts   
      WHERE  ACCOUNT_DESC LIKE '%Eye%'   
      GROUP BY  ACCOUNT_ID

       

      If this can be done on the CF page, that would work as well instead of in the query.. i just need the Flagged values to display alongside each record.