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

[jlig]

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.

[BKBK]

You are on the right track. I actually expected something like

SELECT ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE, IF(PKG_CODE = 'Y', 1, 0) AS FLAG 

[jlig]

Updated.. but it still is not working right..I need it to also check the SUBSCRIBER_ID" column value..

Here is what I get now using the code: IF(PKG_CODE = 'Y', 1, 0) AS FLAG

- Notice that lines 7 & 8 did not go to "1" ?

- But the "10017973" value matches the first one with a PKG_CODE = 'Y'

- I needed those two lines to also go to "1" because they share a common value AND PKG_CODE = 'Y'

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

[BKBK · Accepted solution]

SELECT ACCOUNT_ID, SUBSCRIBER_ID, PKG_CODE, IF(SUBSCRIBER_ID IN

    (SELECT SUBSCRIBER_ID

      FROM   Accounts   

      WHERE  ACCOUNT_DESC LIKE '%Eye%' AND PKG_CODE = 'Y'), 1, 0) AS flag

FROM   Accounts   

WHERE  ACCOUNT_DESC LIKE '%Eye%'

GROUP BY  ACCOUNT_ID

[jlig]

@BKBK, thanks for the SubQuery.. that did the trick!

[BKBK]

Cheers! I have just seen something that could make it more efficient, namely

SELECT DISTINCT SUBSCRIBER_ID