MySQL combine two columns and list all doubles on those columns-CodePudding

I have trouble using CONCAT in a query where I combine the first and last name of people and then finding other records with the same first and last name.

(I want to put all found id's in an PHP array for later use.)

THE PROBLEM

I can't seem to skip the GROUP BY. The query gives a correct count of the doubles found, but I need all the seperate records (the id's other fields). MySQL says I can't use 'fullname' because it isn't a column. I get that, but that combination is what I am searching for.

When using other queries, I keep getting results where it lists doubles on both first and last name - where I want it on the CONCAT fullname.

THE CODE

SELECT 
    id,firstname,lastname,artistname, CONCAT(firstname,' ', lastname) as fullname, COUNT(lastname)
FROM
    table
GROUP BY 
    fullname
HAVING 
       (COUNT(lastname) > 1)

So, the code works, but I'd like to have all the seperate records listed.

Any help in getting me on the right path here is highly appreciated.

CodePudding user response：

You could do something like this:

SELECT A.id, A.firstname, A.lastname, A.artistname,
       CONCAT(A.firstname,' ', A.lastname) AS fullname, cnt
FROM mytable A
JOIN
(SELECT 
    firstname, lastname, COUNT(lastname) cnt
FROM
    mytable
GROUP BY firstname,lastname) B
 ON A.firstname=B.firstname 
 AND A.lastname=B.lastname
WHERE cnt > 1

Modify your attempted query to become a subquery then join the table with it. Matching condition on firstname and lastname then add WHERE cnt > 1 to get match only those having count more than one.

Or perhaps, if you're on MySQL v8 (or MariaDB that supports window function), then consider this method:

SELECT * 
 FROM
(SELECT 
     id, firstname, lastname, CONCAT(firstname,' ', lastname) AS fullname,
     COUNT(*) OVER (PARTITION BY firstname, lastname) cnt
 FROM mytable) V
 WHERE cnt > 1

Using COUNT() OVER () window function then make it as a subquery. Note that you can't use HAVING cnt > 1 directly on the query as it will return you error

Error 3594: You cannot use the alias 'cnt' of an expression containing a window function in this context.'

so the subquery is necessary.

Demo fiddle