Solved

Need help with an SQL query

Posted on 2008-06-20
5
190 Views
Last Modified: 2010-03-19
I need to query a table in my database that contains client records.  I would like to see just the people in this table who have the same first name AND same last name and thier distinct ID number.

In otherwords the results should look something like:

ID, last_name, first_name
-------------------------------
23 Jones, James
45 Jones, James
67 Stewart, Martha
122 Stewart, Martha

Thanks in advance!
0
Comment
Question by:hyphenpipe
  • 2
  • 2
5 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 21831031
Hello hyphenpipe,

Select ID,Last_Name, First_Name From MyTable Where Last_Name = First_Name

Should do that.

Regards,

TimCottee
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21831040
try this:

select id, first_name, last_name
from yourtablename t
join
(
select first_name, last_name
from yourtablename
group by first_name, last_name
) a on t.first_name = a.first_name and t.last_name = a.last_name
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21831046
whoops..one change:

select id, first_name, last_name
from yourtablename t
join
(
select first_name, last_name
from yourtablename
group by first_name, last_name
having count(*)> 1
) a on t.first_name = a.first_name and t.last_name = a.last_name
0
 
LVL 6

Author Comment

by:hyphenpipe
ID: 21831071
I'm sorry, the first and last name don't have to be equal but their whole name, ie find all the clients whose name are James Jones, Martha Stewart, etc.
0
 
LVL 6

Author Comment

by:hyphenpipe
ID: 21831088
chapmandew - yours worked, I just needed to reformat it as:

select id, t.first_name, t.last_name
from yourtablename t
join
(
select first_name, last_name
from yourtablename
group by first_name, last_name
having count(*)> 1
) a on t.first_name = a.first_name and t.last_name = a.last_name
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question