SELECT users that match 1 to many fields and remove duplicates

Question

I am making a list of users. I want to return users (and items) that match specific fields (matches email, matches email & type, matches name & type, or matches all of them). I would like to reduce duplicate users so don't return email if it matches email & type.

Example data:

+----+---------------------+--------+---------+
| id | email               | type   | name    |
+----+---------------------+--------+---------+
| 1  | rod@example.com     | user   | Rod     |
| 2  | jane@example.com    | user   | Jane    |
| 3  | rod2@example.com    | user   | Rod     |
| 4  | rod3@example.com    | admin  | Rod     |
| 5  | helen@example.com   | admin  | helen   |
+----+---------------------+--------+---------+

So if $email = 'rod@example.com', $type = 'user', $name = 'Rod' I want to return the following results. I would like to return the following:

+----------------+----+
| matches        | id |
+----------------+----+
| 3 (all)        | 1  |
| 2 (name, type) | 3  |
| 1 (name)       | 4  |
+----------------+----+

I was thinking of separate sql queries for each match set, 1 for email, another for email & type, another for name & type, and 1 for all. And then filter the results at the end using php.

$users = array();

// match email
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
        FROM users u
        INNER JOIN items i
            ON u.id = i.user_id
        WHERE u.email = '.$email.'
        GROUP BY u.id';
$users[] = mysqli_query($sql);

// match email & type
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
        FROM users u
        INNER JOIN items i
            ON u.id = i.user_id
        WHERE u.email = '.$email.' AND u.type = '.$type.'
        GROUP BY u.id';
$users[] = mysqli_query($sql);

// match name & type
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
        FROM users u
        INNER JOIN items i
            ON u.id = i.user_id
        WHERE u.name = '.$name.' AND u.type = '.$type.'
        GROUP BY u.id';
$users[] = mysqli_query($sql);

// match email, name & type
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
        FROM users u
        INNER JOIN items i
            ON u.id = i.user_id
        WHERE u.email = '.$email.' AND u.name = '.$name.' AND u.type = '.$type.'
        GROUP BY u.id';
$users[] = mysqli_query($sql);

I feel there must be a way to create one query to replace them and avoid any filtering using php. Can anyone offer any suggestions?

Answer 1

This could be the possible query:

select (CASE 

WHEN CONCAT(users.name, '@')=LEFT(users.email, LENGTH(users.name)+1)
and users.type='user' THEN "3 (all)"

WHEN users.type='user' and CONCAT(users.name, '@')!=LEFT(users.email, 
LENGTH(users.name)+1) THEN "2 (name, type)"

ELSE "1 (name)" END)matches, users.id from users 

inner join items on users.id=items.user_id and 

users.name in (select users.name from users 

group by users.name having count(users.name)>1);

OUTPUT:

+----------------+----+
| matches        | id |
+----------------+----+
| 3 (all)        | 1  |
| 2 (name, type) | 3  |
| 1 (name)       | 4  |
+----------------+----+

Answer 2

I'd go for this:

SELECT id,
  email = 'rod@example.com' EmailMatch,
  type = 'user' TypeMatch,
  name = 'Rod' NameMatch
FROM t
HAVING EmailMatch + TypeMatch + NameMatch > 0
ORDER BY EmailMatch + TypeMatch + NameMatch DESC

Output:

| ID | EMAILMATCH | TYPEMATCH | NAMEMATCH |
|----|------------|-----------|-----------|
|  1 |          1 |         1 |         1 |
|  3 |          0 |         1 |         1 |
|  2 |          0 |         1 |         0 |
|  4 |          0 |         0 |         1 |

Fiddle here.

You can also change id for the actual fields in the table and avoid a later join as you would be retrieving the data directly from that very same query.

If you do need to actually see the amount of total matches per row then you can add another row in which you duplicate all the conditions and add them or wrap your query in another select statement. Either way is not easy to read or inefficient. So I'd recommend that if you do need to see the match count per row, then add the match columns in PHP.

Answer 3

I would perform that with a CASE WHEN (sorry if the syntax is not 100% correct):

$sql = 'SELECT u.id, 
  CASE WHEN (u.name = ' .$name. ' AND u.type = '.$type.' AND u.email = '.$email.') THEN "3 (all)"
    ELSE WHEN (u.name = ' .$name. ' AND u.type = '.$type.') THEN "2 (name, type)"
    ELSE WHEN (u.name = ' .$name. ' AND u.emal = '.$email.') THEN "2 (name, email)"
    ELSE WHEN (u.type = ' .$type. ' AND u.email = '.$email.') THEN "2 (email,type)"
    ELSE WHEN (u.name = ' .$name. ') THEN "1 (name)"
    ELSE WHEN (u.type = ' .$type. ') THEN "1 (type)"
    ELSE WHEN (u.email = ' .$email. ') THEN "1 (email)"
    ELSE "0" END AS matches
  FROM users u
  HAVING matches != "0"';