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.
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.