mysql - Get entries which share other entires in an associated table? -

i have 2 tables: users , interests.

interests consists of interest (e.g. "football") , user id.

i query user , other users shares @ least 3 interests don't know how efficiently.

how write sql "get user's interests , fetch other users shares @ least 3 of interests"?

thanks.

select i.interest, i.user_id, count(*) amount # , u.username interests left join interests i2 on i.interest i2.interest # left join users u # on i2.user_id = u.id i.user_id = 1 group i.interest having amount >= 3 

this asume have users (id, username) , `interests (interest, user_id)´. uncomment including username.

consider having interests id references instead of varchar. it's more logical maintain.