Using MySQL, return specific user ID's in a table with duplicate ID's using a second column as the criteria -

here's scenario:

i have table looks this:

table name: user_activities

user_id | activity_code ----------------------- 1111    |  013 ----------------------- 1111    |  112 ----------------------- 2222    |  014 ----------------------- 3333    |  028 ----------------------- 3333    |  245 ----------------------- 3333    |  468 ----------------------- 4444    |  079 ----------------------- 4444    |  028 ----------------------- 5555    |  157 ----------------------- 5555    |  523 ----------------------- 6666    |  081 ----------------------- 7777    |  067 ----------------------- 7777    |  624 ----------------------- 

it's oracle database using mysql, , activity_code char field. need extract user_id's have activity_code starting 0, , have no activity_code's starting other number.

so,

where activity_code '0%' 

gives me user_id's have activity_code starting 0, includes user_id's have activity code starting 0 , other activity_code's starting other numbers.

how return users have activity_code's starting 0?

i need single query (subqueries fine), , can't create views.

can done?

you via 'not in' , using regular expressions.

select distinct user_id user_activities   activity_code '0%'     , user_id not in (select user_id                            user_activities                            activity_code regexp '^[1..9]') 

sql fiddle

putting on way hat, can min/max ascii sort on activity_code, because you've padded 0s.

so simplest possible query.

select user_id user_activities   group user_id   having min(activity_code) '0%'     , max(activity_code) '0%'; 

second sql fiddle example