SELECT
  id,
  name,
  group_concat(
    DISTINCT concat( number, ' (', phones.description, ')' )
    ORDER BY phones.description
    SEPARATOR '<br>'
  ) AS phones,
  group_concat(
    DISTINCT concat( email, ' (', emails.description, ')' )
    ORDER BY emails.description
    SEPARATOR '<br>'
  ) AS emails,
  group_concat(
    DISTINCT concat( address, ' (', addresses.description, ')' )
    ORDER BY addresses.description
    SEPARATOR '<br>'
  ) AS addresses

FROM people
LEFT OUTER JOIN phones
  ON phones.person_id = id
LEFT OUTER JOIN emails
  ON emails.person_id = id
LEFT OUTER JOIN addresses
  ON addresses.person_id = id

WHERE id IN (
  SELECT DISTINCT person_id
  FROM phones
  WHERE number LIKE ?
  AND person_id IN (
    SELECT DISTINCT person_id
    FROM emails
    WHERE email LIKE ?
    AND person_id IN (
      SELECT DISTINCT person_id
      FROM addresses
      WHERE address LIKE ?
    )
  )
)
AND name LIKE ?
GROUP BY id
ORDER BY id DESC;