How do i go on about on a query for where i wanna extract the same first name but the last name is different?
NAME
------
Chris Stutter
Chris Lamb
Alfred Dark
Kristine Light
output:
Chris Stutter
Chris Lamb
How do i go on about on a query for where i wanna extract the same first name but the last name is different?
NAME
------
Chris Stutter
Chris Lamb
Alfred Dark
Kristine Light
output:
Chris Stutter
Chris Lamb
2
Answers
I’ve made a script for you for this specific condition. Based on the info you shared, I’ve created the test scripts below. You can test this script and see the result really quickly at https://onecompiler.com/mysql/
You should be able to utilize this fetch script as a reference and modify it accordingly for your own purpose now.
You want to work with first name and last name in your database, but your table doesn’t provide that information. It only has a column for the full name. This means that your database design is not appropriate for the task. The information you seek is not stored atomic, but in a concatenated form and thus violates the first normal form. (This also shows that database normalization sometimes depends on how you want to work with the data.) The best way to deal with this problem is hence to change your database and make first and last name separate columns.
If you cannot change the database design, the first task is to find out in which formats the names are stored. So far you have shown "first name – one blank – last name". If this is the only format, then it is rather easy to split the two. If, however, you also have to deal with ‘Smith, John’ or ‘Arthur Conan Doyle’, it gets more complex. Let’s say all names are in the same format. So, split first name and last name and work with these.
Once you have separate first and last name, the task becomes easy. You are looking for names for which exists another last name with the same first name, i.e. use
EXISTS
.