Java MySQL COUNT query in case of a relation of two rows

Question

I am developing a web application in java. There I use several MySQL tables.

One of them marks 'relationships' between two objects. So i created a table:

id  username    relation    timestamp
1   user1       user2   2014-11-18 12:20:03
2   user2       user1   2014-11-18 12:20:03

a user is related to another when this user marked the other user as 'related' AND the second user marked the first as well.

in case of

id  username    relation    timestamp
3   user1       user3   2014-11-18 12:20:03

there is no relationship.

I can count the amount of one-sided relationships of a user. But now I need to count the amount of real (two-sided) relationships of one user. Can I use the COUNT command?

krokodilko · Accepted Answer · 2014-11-18 20:58:58Z

1

I am assumming that there is an unique constraint on columns username + relation, so there it is impossible to enter the same relation twice, e.g

id  username    relation    timestamp
1   user1       user2   2014-11-18 12:20:03
2   user1       user2   2014-11-18 12:20:03

Under the above assumption, in order to count "two-sided" relations of a particular user, a semi join can be used:

SELECT count(*)
FROM table1 x1
WHERE EXISTS(
  SELECT 1 FROM table1 x2
  WHERE x1.`username` = x2.`relation`
    AND x1.`relation` = x2.`username`
)
AND x1.`username` ='user1';

demo: http://sqlfiddle.com/#!2/67db14/2

I am also assuming that there is some constraint on the table that prevents from entering self-related users, for example:

id  username    relation    timestamp
1   user1       user1   2014-11-18 12:20:03

edited Nov 18, 2014 at 20:58

answered Nov 18, 2014 at 20:53

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java MySQL COUNT query in case of a relation of two rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related