CODEWITHSUNDEEP
mysqlsql
Preethy
Preethy

Reputation: 722

Filter count table query

i have 2 tables as following.

User

id         name 
---------------
1          john
2          raju
3          manu
4          raghu

friendtable

id  userid       recvId
------------------------
1   1         2
2   1         3
3   2         3
4   3         4

Is it possible to filter users by their friends count from these tables.Please help me.

For eg :- range >=3 will result : john,manu

range >3 and range <2 will result : raju range <2 result : raghu

Upvotes: 0

Views: 38

Answers (2)

user3440718
user3440718

Reputation: 1

SELECT name FROM user a,friendtable b WHERE a.id=b.id AND  b.recvid>=3

SELECT name FROM  user a,friendtable b WHERE a.id=b.id and  b.recvid>3 AND b.recid<2

SELECT name FROM  user a,friendtable b WHERE a.id=b.id AND  b.recid<2

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Do a UNION ALL to get all id's from friendstable in one column. Join users table with that result.

Do a GROUP BY, adjust HAVING to decide what to return, e.g. at least 3 times etc.

select u.name
from users
join (select userid as id from friendtable
      union all
      select recvId as id from friendtable) f
  on u.id = f.id
group by u.name
having count(*) >= 3

Upvotes: 1

Related Questions