Count friends in common using LEFT JOIN

Question

I have the following tables:

users:

id | usuario | pnome | snome | foto
1  | Igor    | Igor  | Souza | perfil.png
2  | Alex    | Alex  | Khal  | foto.jpg
3  | Maria   | Maria | Silva | foto.png

friendships:

id | amigo1 | amigo2 | estatus (2 significa que são amigos)
1  | Igor   | Alex   | 2
2  | Igor   | Maria  | 2
3  | Maria  | Alex   | 2

In the table above, amigo1 is who sent the invitation of friendship...

I need to know which user friends of the accessed page ($userPagina) and their information (pnome, snome, foto), so far so good, I did the following query:

SELECT u.usuario, u.pnome, u.snome, u.foto
FROM amizades a
INNER JOIN usuarios u
ON (u.usuario = a.amigo1 AND a.amigo1 <> '$userPagina') OR (u.usuario = a.amigo2 AND a.amigo2 <> '$userPagina')
WHERE (a.amigo1 = '$userPagina' OR a.amigo2 = '$userPagina') AND a.estatus = 2

So, when accessing meusite.com/Alex he returns his friends (Igor and Maria) correctly, but I also want to know how many friends in common Alex’s friends ($userPagina) has with the logged-in user who accessed your profile ($userLogado), that is, how many mutual friends Igor and Maria have with the $userLogado. So I tried adding the following data in the query:

SELECT u.usuario, u.pnome, u.snome, u.foto, COUNT(c.id) as comum
FROM amizades a
INNER JOIN usuarios u
ON (u.usuario = a.amigo1 AND a.amigo1 <> '$userPagina') OR (u.usuario = a.amigo2 AND a.amigo2 <> '$userPagina')
LEFT JOIN (SELECT id FROM amizades WHERE (amigo1 = u.usuario AND amigo1 <> '$userLogado' AND estatus = 2) OR (amigo2 = u.usuario AND amigo2 <> '$userLogado' AND estatus = 2)) c ON (u.usuario = A.amigo1 OR u.usuario = A.amigo2)
WHERE (a.amigo1 = '$userPagina' OR a.amigo2 = '$userPagina') AND a.estatus = 2

However, when accessing meusite.com/Alex again, only the error is returned:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.usuario' in 'where clause'

But I did not understand the reason for this mistake, since I am identifying the u. in the INNER JOIN... How to tidy up? And, my logic for this count is correct?

Table structure:

users:

CREATE TABLE IF NOT EXISTS `usuarios` (
      `id` int(11) NOT NULL,
      `usuario` varchar(90) NOT NULL,
      `pnome` varchar(30) NOT NULL,
      `snome` varchar(60) NOT NULL,
      `foto` varchar(90) NOT NULL
    ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `usuarios` (`id`, `usuario`, `pnome`, `snome`, `foto`) VALUES
(5, 'Igor', 'Igor', 'Souza', 'perfil.png'),
(4, 'Alex', 'Alex', 'Khal', 'foto.jpg'),
(3, 'Maria', 'Maria', 'Silva', 'foto.png');

ALTER TABLE `usuarios`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `usuarios`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=13;

friendships:

CREATE TABLE IF NOT EXISTS `amizades` (
  `id` int(11) NOT NULL,
  `amigo1` varchar(90) NOT NULL,
  `amigo2` varchar(90) NOT NULL,
  `estatus` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `amizades` (`id`, `amigo1`, `amigo2`, `estatus`) VALUES
(10, 'Igor', 'Alex', 2),
(9, 'Igor', 'Maria', 2),
(8, 'Maria', 'Alex', 2);

ALTER TABLE `amizades`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `amizades`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=13;

Sample image:

Answer 1

By the question I understood that you want all friends in common between two users.

SELECT u.usuario, u.pnome, u.snome, u.foto, COUNT(c.id) as comum
FROM amizades a
INNER JOIN usuarios u
ON (u.usuario = a.amigo1 AND a.amigo1 <> '$userPagina') OR (u.usuario = a.amigo2 AND a.amigo2 <> '$userPagina')
LEFT JOIN (SELECT id FROM amizades WHERE (amigo1 = u.usuario AND amigo1 <> '$userLogado' AND estatus = 2) OR (amigo2 = u.usuario AND amigo2 <> '$userLogado' AND estatus = 2)) c ON (u.usuario = A.amigo1 OR u.usuario = A.amigo2)
WHERE (a.amigo1 = '$userPagina' OR a.amigo2 = '$userPagina') AND a.estatus = 2

Note that within the sub-query that is found after LEFT JOIN you are using u.usuario, which exists only outside this query. This is called a correlation variable (variable correlation) and in SQL you can only use it if you place the SIDE keyword after LEFT JOIN and before the sub-query. This only happens if your sub-query is within the FROM clause.

Unfortunately Mysql does not yet implement the SIDE keyword, so if you do a sub-query within the FROM clause you cannot use any correlation variable, that is, no variable defined outside the sub-query itself. But since this does not apply to the WHERE clause, you can normally use a sub-query with correlation variable in this clause.

I’ll show you a slightly different solution, easier to understand. Since you already have the query that returns all friends of a person, just find all the friends of the logged user, all the friends of the user of the page and see which are in the two tables. This would be very easy with the use of INTERSECT, but Mysql does not have this implementation. So I made an intersection of the two sets with an IN in the WHERE clause:

SELECT COUNT(conta.usuario) FROM (
  SELECT u.usuario, u.pnome, u.snome, u.foto
  FROM amizades a
  INNER JOIN usuarios u
  ON (u.usuario = a.amigo1 AND a.amigo1 <> 'Alex') OR (u.usuario = a.amigo2 AND a.amigo2 <> 'Alex')
  WHERE (a.amigo1 = 'Alex' OR a.amigo2 = 'Alex') AND a.estatus = 2 AND (u.usuario, u.pnome, u.snome, u.foto) IN (
    SELECT u2.usuario, u2.pnome, u2.snome, u2.foto
    FROM amizades a2
    INNER JOIN usuarios u2
    ON (u2.usuario = a2.amigo1 AND a2.amigo1 <> 'Igor') OR (u2.usuario = a2.amigo2 AND a2.amigo2 <> 'Igor')
    WHERE (a2.amigo1 = 'Igor' OR a2.amigo2 = 'Igor') AND a2.estatus = 2
  )
) conta;

Notice that one query takes all of Alex’s friends and the other takes all of Igor’s friends. Then you group in order to get all of Alex’s friends who are (IN) in the table of Igor’s friends, getting all of Igor’s friends in common. After that you use COUNT to know how many friends there are in this table of users who are friends of Alex and Igor.

Here then is the consultation you want, featuring all of Alex’s friends and how many friends he has in common with you:

SELECT amc.usuario, amc.pnome, amc.snome, amc.foto, COUNT(a.id) as amigos_em_comum
FROM
(
  SELECT am.usuario as usuario, am.pnome, am.snome, am.foto, u.usuario as amigo
  FROM 
  (
    SELECT u.usuario as usuario, u.pnome as pnome, u.snome as snome, u.foto as foto
    FROM amizades a 
    INNER JOIN usuarios u 
    ON (u.usuario = a.amigo1 AND a.amigo1 <> 'Alex') OR (u.usuario = a.amigo2 AND a.amigo2 <> 'Alex')
    WHERE (a.amigo1 = 'Alex' OR a.amigo2 = 'Alex') AND a.estatus = 2
  ) as am, amizades as a, usuarios as u 
  WHERE (am.usuario = a.amigo1 OR am.usuario = a.amigo2) AND (u.usuario = a.amigo1 OR u.usuario = a.amigo2) AND am.usuario <> u.usuario AND u.usuario <> 'Igor'
  ORDER BY am.usuario
) as amc, amizades as a
WHERE (amc.amigo = a.amigo1 OR amc.amigo = a.amigo2) AND (a.amigo1 = 'Igor' OR a.amigo2 = 'Igor') AND amc.usuario <> 'Igor'
GROUP BY amc.usuario, amc.pnome, amc.snome, amc.foto
ORDER BY amc.usuario

I hope I’ve helped!

Answer 2

Puts u.user to be the result of the first query

SELECT u.pnome, u.snome, u.foto, u.usuario, COUNT(c.id) as comum
FROM amizades a
INNER JOIN usuarios u
ON (u.usuario = a.amigo1 AND a.amigo1 <> '$userPagina') OR (u.usuario = a.amigo2 AND a.amigo2 <> '$userPagina')
LEFT JOIN (SELECT id FROM amizades WHERE (amigo1 = u.usuario AND amigo1 <> '$userLogado' AND estatus = 2) OR (amigo2 = u.usuario AND amigo2 <> '$userLogado' AND estatus = 2)) c ON (u.usuario = A.amigo1 OR u.usuario = A.amigo2)
WHERE (a.amigo1 = '$userPagina' OR a.amigo2 = '$userPagina') AND a.estatus = 2

note the first line of select