SQL - Want to output the ones with the same first name but different last name. Full name in the sam-CodePudding

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

CodePudding user response：

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.

WITH names AS
(
  SELECT
    SUBSTRING_INDEX(name, ' ', 1) AS first_name,
    SUBSTRING_INDEX(name, ' ', -1) AS last_name
  FROM mytable
)
SELECT first_name, last_name
FROM names
WHERE EXISTS
(
  SELECT NULL
  FROM names other
  WHERE other.first_name = names.first_name
  AND other.last_name <> names.last_name
)
ORDER BY first_name, last_name;

CodePudding user response：

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/

-- create
CREATE TABLE YOUR_TABLE (
  Name TEXT NOT NULL
);

-- insert
INSERT INTO YOUR_TABLE VALUES ('Chris Stutter');
INSERT INTO YOUR_TABLE VALUES ('Chris Stutter');
INSERT INTO YOUR_TABLE VALUES ('Chris Stutter');
INSERT INTO YOUR_TABLE VALUES ('Chris Lamb');
INSERT INTO YOUR_TABLE VALUES ('Alfred Dark');
INSERT INTO YOUR_TABLE VALUES ('Alfred Dark');
INSERT INTO YOUR_TABLE VALUES ('Kristine Light');

-- fetch
SELECT DISTINCT *
FROM YOUR_TABLE
WHERE SUBSTRING_INDEX(Name, ' ', 1) IN (
  SELECT *
  FROM (
    SELECT SUBSTRING_INDEX(Name, ' ', 1) as firstName
    FROM YOUR_TABLE
    GROUP BY firstName
    HAVING COUNT(DISTINCT Name) > 1
  ) AS DuplicatedFirstNames
);

You should be able to utilize this fetch script as a reference and modify it accordingly for your own purpose now.