Finding matchs on one column, but only if another column matches too

[JVassie]

Not sure I'm going about this in the correct way, but I guess its exploring what does what!

I have a table called contacts, within which there are duplicate rows (first and last are the same with different ids). I have a page where I display each of the duplicates using the following code. How it currently works is it displays each of rows in the table with the same surname, regardless of if they have the same first name.

What I dont know is how would be best to go about showing only those records where there are two or more results for rows with the same first and last name.

Any ideas from anyone please?

$q = "SELECT first, last FROM contacts GROUP BY last ORDER BY last, first";
$result = mysql_query($q,$conn);
while($row = mysql_fetch_assoc($result))
	{
            $q2 = "SELECT * FROM contacts WHERE last = '$row[last]' ORDER BY first";
            $result2 = mysql_query($q2,$conn);
            $count = mysql_num_rows($result2);
            while($row2 = mysql_fetch_assoc($result2))
            {
              if($count > 1)
              {
// Code to display the duplicates

[JVassie]

Half way solved it I think

$q = "SELECT first, last, count(*) FROM contacts GROUP BY last, first HAVING count(*) > 1 ORDER BY last, first";

Replacing the original query with that new one means I got no results on the duplicate page (even ones with differing first names). To test I added another record with a duplicate first and last name.

What was then shown on the duplicates page was 3 records. All 3 had the same last name, but the first names were James, James, Oliver.

What would I need to modify to stop the Oliver record showing. If I delete the newly made duplicate, the duplicates page empties as expected.

[tomhath]

$q2 = "SELECT * FROM contacts WHERE last = '$row[last]' and first = '$row[first]' ORDER BY first";

[JVassie]

tomhath;10970724 wrote:

$q2 = "SELECT * FROM contacts WHERE last = '$row[last]' and first = '$row[first]' ORDER BY first";

Unfortunately I don't think that will work, as it will pull a record that matches the first query even if there is no duplicate.

[Lars_Berg]

select first, 
       last
  from contacts 
 where last in
      (select last 
         from contacts
        group by last
       having count(*) = 1)
 order by last, 
       first