How to find intersect rows when condition depend on some columns in one table
- by user3695637
Table subscribe
subscriber | subscribeto (columns)
1 | 5
1 | 6
1 | 7
1 | 8
1 | 9
1 | 10
2 | 5
2 | 6
2 | 7
There are two users that have id 1 and 2. They subscribe to various user and I inserted these data to table subscribe. Column subscriber indicates who is subscriber and column subscribeto indicates who they've subscribe to. From the above table can conclude that; user id=1 subscribed to 6 users
user id=2 subscribed to 3 users
I want to find manual of subscription (like Facebook is manual friends)
user 1 subscribe to user 5,6,7,8,9,10
user 2 subscribe to user 5,6,7
So, Manual subscription of user 1 and 2 are: 5,6,7
And I'm trying to create SQL statement..
I give you user table for my SQL statement and I think we can use only subscribe table but I can't figure out.
Table user
userid (columns)
1
2
3
...
...
SQL
"select * from user where (select count( 1 ) from subscribe where subscriber = '1' and subscribeto = user.userid) and (select count( 1 ) from subscribe where subscriber = '2' and subscribeto = user.userid);"
This SQL can work correctly, but it very slow for thousands of columns. Please provide better SQL for me, Thanks.
