SQL search people by data in 4 tables

Question

I'm trying to make a search engine that finds people based on information from the people table and 3 other tables (phones, emails, addresses). The user can pick 0 to 4 search parameters (see WHERE clause in my sql code), if 0 parameters are chosen, the desired output is the entire database.

I could search one table at a time and remember the IDs, but that seems inefficient.

I also have this SQL that can do everything in a single query, but it doesn't work exactly as intended:

SELECT 
  people.id,
  name,
  group_concat(
    DISTINCT concat( number, ' (', phones.description, ')' )
    ORDER BY phones.description
    SEPARATOR '\n'
  ) AS phones,
  group_concat(
    DISTINCT concat( email, ' (', emails.description, ')' )
    ORDER BY emails.description
    SEPARATOR '\n'
  ) AS emails,
  group_concat(
    DISTINCT concat( address, ' (', addresses.description, ')' )
    ORDER BY addresses.description
    SEPARATOR '\n'
  ) AS addresses
FROM people
LEFT OUTER JOIN phones
  ON phones.person_id = people.id
LEFT OUTER JOIN emails
  ON emails.person_id = people.id
LEFT OUTER JOIN addresses
  ON addresses.person_id = people.id
WHERE
  name LIKE ?
  AND number LIKE ?
  AND email LIKE ?
  AND address LIKE ?
GROUP BY people.id
ORDER BY people.id DESC

The problems with this query are:

1. If I search by phone number and someone has more than one phone number, the resulting query will include only the number I searched for and not this person's other number.
2. It is extremely inefficient, iterating over the database more than necessary and returning the same entires several times (which is why I ended up using DISTINCT in the group_concat(...) function).

SQL commands to create a sample database:

CREATE TABLE people (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120)
);

CREATE TABLE phones (
person_id INT UNSIGNED NOT NULL,
number VARCHAR(25) NOT NULL,
description VARCHAR(25)
);

CREATE TABLE emails (
person_id INT UNSIGNED NOT NULL,
email VARCHAR(100) NOT NULL,
description VARCHAR(25)
);

CREATE TABLE addresses (
person_id INT UNSIGNED NOT NULL,
address VARCHAR(200) NOT NULL,
description VARCHAR(25)
);

Answer 1

After @Barmar pointed me in the right direction, I came up with this code:

SELECT
  id,
  name,
  group_concat(
    DISTINCT concat( number, ' (', phones.description, ')' )
    ORDER BY phones.description
    SEPARATOR '\n'
  ) AS phones,
  group_concat(
    DISTINCT concat( email, ' (', emails.description, ')' )
    ORDER BY emails.description
    SEPARATOR '\n'
  ) AS emails,
  group_concat(
    DISTINCT concat( address, ' (', addresses.description, ')' )
    ORDER BY addresses.description
    SEPARATOR '\n'
  ) 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;

Answer 2

To avoid creating a cross product between all the child tables, do those searches in subqueries.

To get all the information from the child table related to the person, not just the matching rows, do a second join.

SELECT 
  people.id,
  name,
  group_concat(DISTINCT
    concat(number, ' (', p1.description, ')' )
    ORDER BY p1.description
    SEPARATOR '\n'
  ) AS phones,
  group_concat(DISTINCT
    concat(email, ' (', e1.description, ')' )
    ORDER BY e1.description
    SEPARATOR '\n'
  ) AS emails,
  group_concat(DISTINCT
    concat(address, ' (', a1.description, ')' )
    ORDER BY a1.description
    SEPARATOR '\n'
  ) AS addresses
FROM people
LEFT OUTER JOIN (
     SELECT DISTINCT person_id
     FROM phones
     WHERE number LIKE ?) AS phones
  ON phones.person_id = person.id
LEFT OUTER JOIN phones AS p1 ON p1.person_id = person.id
LEFT OUTER JOIN (
     SELECT DISTINCT person_id
     FROM emails
     WHERE email LIKE ?) AS emails
  ON emails.person_id = person.id
LEFT OUTER JOIN emails AS e1 ON e1.person_id = person.id
LEFT OUTER JOIN (
     SELECT DISTINCT person_id
     FROM addresses
     WHERE address LIKE ?) AS addresses
  ON addresses.person_id = person.id
LEFT OUTER JOIN addresses AS a1 ON a1.person_id = person.id
WHERE
  name LIKE ?
GROUP BY people.id
ORDER BY people.id DESC