sql server - How can I return rows from an SQL query where one unique ID satisfies conditions in different rows? -

i have table users has foreign key in other table, usershavesocialmediafans.

usershavesocialmediafans has columns:

socialmediaid, userid, fancount 

i need find users have fan count greater (or less than) amount for each social media. admins need able filter out users might have @ least 6 facebook fans , 7 twitter followers.

it super easy when had information in user table:

userid, ..., facebookfans, twitterfollowers, ...  ---0--, ---, -----10-----, ------1000------, ... 

then, select userid users facebookfans > # , twitterfollowers > #; however, data each user split multiple rows , don't know how handle logic. feel there must simple answer, can't think of right now.

you need join usershavesocialmediafans table twice it. example, if wanted more 1,000 facebook fans, , more 10 twitter fans, following:

select  distinct u.userid    users                       u join    usershavesocialmediafans    f   on  f.userid = u.userid                                          , f.socialmediaid = <facebooksocialmediaid> join    usershavesocialmediafans    t   on  t.userid = u.userid                                         , t.socialmediaid = <twittersocialmediaid>   f.fancount > 1000 ,     t.fancount > 10; 

if have table stores name of socialmediaid each platform, should join there instead of hard-coding in socialmediaid's each platform. don't know schema, point in right direction.

if, hypothetically, had table named socialmedia had socialmediaid , name of each platform, following:

select  distinct u.userid    users                       u join    usershavesocialmediafans    f   on  f.userid = u.userid  join    socialmedia                 sf  on  sf.socialmediaid = f.socialmediaid                                         , sf.name = 'facebook' join    usershavesocialmediafans    t   on  t.userid = u.userid join    socialmedia                 st  on  st.socialmediaid = t.socialmediaid                                         , st.name = 'twitter'   f.fancount > 1000 ,     t.fancount > 10; 

hopefully helps!