Mysql how to group by multiple columns, and select a flag value based on which group matched
<p>Suppose I want to take all duplicate values of a table by following matched criteria's:
1- matched by (first_name,last_name,father_name).
2- matched by (nationalId).
3- matched by (phone_number).</p>

<p>Suppose I want to get all users whose registered them self more than one using above conditions.
Now i want to take those duplicate records, along the other columns a i need to select a a column <code>matched_type</code> that should has one of these <code>1</code>,<code>2</code>,<code>3</code> values based on what matched occurred.
Now i have separate query for each matching criteria, is it possible to do it with one query? </p>

<p>I have these queries:</p>

<pre><code>SELECT u.first_name,u.last_name,u.father_name,u.national_id,u.phone,COUNT(u.id) AS total,'1' AS match_type
FROM users
GROUP BY CONCAT(u.first_name,u.last_name,u.father_name)
HAVING total &gt; 1
</code></pre>

<p>and for second matching criteria:</p>

<pre><code>SELECT u.first_name,u.last_name,u.father_name,u.national_id,u.phone,COUNT(u.id) AS total, '2' AS match_type
FROM users
GROUP BY u.national_id
HAVING total &gt; 1
</code></pre>

<p>and for the last one:</p>

<pre><code>SELECT u.first_name,u.last_name,u.father_name,u.national_id,u.phone,COUNT(u.id) AS total, '3' AS match_type
FROM users
GROUP BY u.phone
HAVING total &gt; 1
</code></pre>

<p>And then i have like this:</p>

<pre><code>SELECT src.* FROM (first_query UNION ALL second_query UNION ALL third_query)
</code></pre>