list entries by surname only if more than one exists in DB

[jasonc310771]

i wish to list entries where one of the fields is repeated elsewhere in the DB

say i want to list all the surnames of people with the name 'smith' but only if their is more than one all the single 'smiths' are not used for this.

the field is as you would expect, 'surname'

what query should i use to obtain these records?

[guelphdad]

use a COUNT in a HAVING clause

[jasonc310771]

having re read my post i see that it may have come across that i only want to list the records with 'smith'

i need to have any surname that has more than one record, also i dont know how to use the count or having code

please can you tell me the query i should use.

[Fumigator]

Looks like this:


SELECT COUNT(*), last_name
FROM table1
GROUP BY last_name
HAVING COUNT(*) > 1

[jasonc310771]

ok that sort of works but i need to have access to all the fields in the records of the results.


$members= mysql_query("SELECT COUNT(*), `f_name`, `l_name` FROM `users` GROUP BY `l_name` HAVING COUNT(*) > 1") or die(mysql_error());
for ($i = 0; $i < mysql_num_rows($members); $i++) {
?><br>f_name: <?=mysql_result($members, $i, "f_name");?>&nbsp;l_name: <?=mysql_result($members, $i, "l_name");?>
<? } ?>


but not showing every result only the first or last one not which but only showing one result for the groups of repeats surnames.

[guelphdad]

get rid of all the rest of your code that isn't mysql related. work directly with your mysql code first to get what you want done.

what you need then is a subquery:


SELECT
field1,
field2,
field3,
etc
FROM
yourtablename
WHERE
lastname IN
(SELECT
last_name
FROM
table1
GROUP BY
last_name
HAVING
COUNT(*) > 1
)