Mysql Query – Set 3

Question:

You are given a network table. Table contains 3 col’s – friend1, friend2, weight.

+———-+——–+——–+—+
|friend1   | friend2  | weight |
+———-+———-+——–+—+
| A         | B         | 5       |
| B         | C         | 6       |
| B         | D         | 2       |
| D         | E         | 9       |
| A         | C        | 6        |
+———-+———-+——–+—+

You have to find a weight between 2nd level friends. i.e if A->B and B->C, you have to find sum of weight of A->C that is sum of A->B and B->C weight’s in Descending order of weight. Your query should not return results if pair A->C already in table.

Solution:

SELECT N1.friend1,N2.friend2,(N1.weight+N2.weight) as rank
FROM network as N1, network as N2
WHERE N1.friend2=N2.friend1  and N2.friend2
      NOT IN (SELECT friend2 FROM network where friend1=N1.friend1)
ORDER BY rank DESC ;

Output:

+———-+——–+——–+——-+
|friend1   | friend2  | weight |
+———-+———-+——–+——+
| B         | E         | 11      |
| A         | D         | 7        |
+———-+———-+——–+——+

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation