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

i have following query looks customer accounts:

select    account_id, subscriber_id, pkg_code    
 from   accounts    
 where  account_desc like '%eye%'    
 group by  account_id

which gives me 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 display "1" in flag column if "any one" of "subscriber_id's" have value of "y" in pkg_code column?

here query results i'm trying example (notice last 4 records need flagged "1" because 1 of them had "y" pkg_code , 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 following, flags "y" records? need check 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 can done on cf page, work instead of in query.. need flagged values display alongside each record.

you on right track. expected

 

select account_id, subscriber_id, pkg_code, if(pkg_code = 'y', 1, 0) flag 

More discussions in ColdFusion

adobe