Jignesh M. Mehta Jignesh M. Mehta - 2 months ago 6
MySQL Question

SQL SELECT Query for appending an "and" before the last car

I have a table called

carDetail
with the following data.

Table

I just want the records who have Car more than "Jignesh" in following format,

"Gaurav have Audi, BMW, Honda And Toyota car"

SELECT concat(`Name`, ' has ', GROUP_CONCAT(car SEPARATOR ', '),' Cars') as
result
from cardetail
where `Name` IN (SELECT `Name` FROM `cardetail`
GROUP BY `Name`
HAVING COUNT(`Car`) > (select count(`Car`)
from cardetail
where `Name` = 'Jignesh'))
GROUP BY `Name`


and got the result something like this,

Result

I want "and" before last Car, something like this,

Gaurav has Audi, BMW, Honda and Toyota Cars


If anyone knows the solution, please help me.

Thanks.

Answer

try this:

    SET @var := (SELECT concat(`Name`, ' has ', GROUP_CONCAT(car SEPARATOR ' ,'),' Cars') as
         result from cardetail where `Name` IN (SELECT `Name` FROM `cardetail` GROUP BY 
        `Name` HAVING COUNT(`Car`) > (select count(`Car`) from cardetail where 
        `Name` = 'Jignesh')) GROUP BY `Name`);

   SELECT REPLACE(@var,SUBSTRING_INDEX(@var, ' ', -2),REPLACE(SUBSTRING_INDEX(@var, ' ', -2),',',' and ')) as result