Count friends in common using LEFT JOIN

By the question I understood that you want all the 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 after LEFT JOIN you are using u.user, which exists only outside this query. This is called a correlation variable and in SQL you can only use it if you put the LATERAL keyword after the LEFT JOIN and before the subquery. This only happens if your subquery is within the FROM clause.

Unfortunately, MySQL does not yet implement the LATERAL keyword, so if you make a sub-query within the FROM clause you can not use any correlation variables, that is, no variables defined outside the sub-query itself. But since this does not apply to the WHERE clause, you can usually use a subquery with a correlation variable in this clause.

I'll show you a slightly different solution, easier to understand. As you already have the query that returns all the friends of a person, just find all the friends of the user logged in, all the friends of the user of the page and see which are in the two tables. This would be very easy with INTERSECT, but MySQL does not have this implementation. So I intersected 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;

Note that one query takes all Alex's friends and the other takes all Igor's friends. Then you group together to get all of Alex's friends that are (IN) on Igor's friends table, getting all the friends in common. After that you use COUNT to find out how many friends there are in this table of users who are friends of Alex and Igor.

Here's the query 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 have helped!

    

Places the 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