469,927 Members | 1,747 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Duplicates

Hi

I wish to perform a search on a column of a table. The column contains
surnames. I wish the results to return the entries which contain more than
one instance of a surname. eg.

ID__Surname
1___Jones
2___Smith
3___Jones
4___Carter
5___Jones
6___Wilson
7___Smith

The result I'm after is

1___Jones
3___Jones
5___Jones
2___Smith
7___Smith

Is this possible with one query? and if so what?

Thanks

Stephen
Aug 8 '06 #1
1 2021

Stephen Preston wrote:
Hi

I wish to perform a search on a column of a table. The column contains
surnames. I wish the results to return the entries which contain more than
one instance of a surname. eg.

ID__Surname
1___Jones
2___Smith
3___Jones
4___Carter
5___Jones
6___Wilson
7___Smith

The result I'm after is

1___Jones
3___Jones
5___Jones
2___Smith
7___Smith

Is this possible with one query? and if so what?

Thanks

Stephen
Can you use subqueries? If so, this would work:

SELECT t1.id, t1.lastname
FROM (

SELECT DISTINCT lastname, count( lastname )
FROM people
GROUP BY lastname
HAVING count( lastname ) >1
)t2
LEFT JOIN people t1 ON t1.lastname = t2.lastname

Aug 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by vsgdp | last post: by
14 posts views Thread by ak | last post: by
3 posts views Thread by ryan.paquette | last post: by
Thekid
3 posts views Thread by Thekid | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.