Similar microblog inference customer relationship SQL statement

Categories like Sina Weibo's attention and common concern

Do not know how others are designed.

Anyway I was designed for example the following

ID USER FRIEND

1 A B

2 B A

3 A C

ID is self-increment

User is the person who initiated the followers friend as a concern

Today's needs such as the following.

Give a, b two user ID how to infer the relationship between A and b very easy to. is a concern B. Or a with B mutual attention a SQL as far as possible the use of the index, without or, as high as possible (do not ask B to pay attention to a)

SELECT *
From xxxxx
where user= ' A ' and friend= ' B '
Union All
SELECT *
From xxxxx
where user= ' B ' and friend= ' A '

CREATE TABLE Tb_user_concern
(ID int Auto_increment primary KEY,
USER varchar (32),
FRIEND varchar (32)
);

--queries and a mutual attention of the user
SELECT *
From Tb_user_concern A
where user = ' A '
and exists
(SELECT *
From Tb_user_concern b
where A.user = B.friend
and a.friend = B.user);

--Query a concerned, but not concerned about the user of a
SELECT *
From Tb_user_concern A
where user = ' A '
And NOT EXISTS
(SELECT *
From Tb_user_concern b
where A.user = B.friend
and a.friend = B.user);

--query attention a, but a not concerned about the user
SELECT *
From Tb_user_concern A
where friend = ' A '
And NOT EXISTS
(SELECT *
From Tb_user_concern b
where A.user = B.friend
and a.friend = B.user);

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

Similar microblog inference customer relationship SQL statement