Select all duplicate rows based on one or two columns?

Question

I Have a table named contacts with fields

+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+

I want to display all duplicates based on first_name and (/ or) last_name, e.g:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | mukta      | chourishi |
|  2 | mukta      | chourishi |
|  3 | mukta      | john      |
|  4 | carl       | thomas    |
+----+------------+-----------+

If searched on just first_name it should return:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

But if searched on both first_name and last_name should return:

+----+
| id |
+----+
|  1 |
|  2 |
+----+

Answer 1

One way to achieve your result is using nested query and having clause: In inner query select those having count more then one, and in outer query select id:

Check following example for single column selection criteria:

Create table:

CREATE TABLE `person` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `first` varchar(120) NOT NULL,
    `last` varchar(120) NOT NULL
);

Insert tuple:

INSERT INTO `person` ( `first`, `last`) VALUES
("mukta", "chourishi"),
("mukta", "chourishi"),
("mukta", "john"),
("carl", "thomas" );

The result you need:

mysql> SELECT  `id` 
    -> FROM `person` 
    -> WHERE `first`=(SELECT `first` FROM `person` HAVING COUNT(`first`) > 1);
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

[ANSWER]

But as if you selection criteria is on the basis of more than one columns then you can make use of JOIN.

To explain it I am writing a selection query that creates an intermediate table that will be use in JOIN as second operand table.

Query is select all fist name and column those duplicates with some of other rows:
For example select rows in which first and last name repeats

mysql> SELECT `first`, `last`,  count(*)  as rows 
    -> FROM `person` 
    -> GROUP BY `first`, `last` 
    -> HAVING count(rows) > 1;
+-------+-----------+------+
| first | last      | rows |
+-------+-----------+------+
| mukta | chourishi |    2 |
+-------+-----------+------+
1 row in set (0.00 sec)

So you have only one pair of first and last names those repeats (or is duplicates with some other rows).

Now, question is: how to select id of this row? Use Join! as follows:

mysql> SELECT  p1.`id`
    -> FROM `person` as p1
    -> INNER JOIN (
    ->     SELECT `first`, `last`,  count(*)  as rows
    ->     FROM `person` 
    ->     GROUP BY `first`, `last` 
    ->     HAVING count(rows) > 1) as p
    -> WHERE p.`first` = p1.`first` and p.`last` = p1.`last`;  
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.06 sec)

you can select on the basis of as many columns as you wants e.g. single column if you want using join then remove last name.