I am having trouble with making an inner join whilst concatenating two of the fields. I can do both separately but have not yet mastered both at the same time.
Here is what I have. It does work, however not in the intended way. It produces the only two results in my database but this is simply a coincidence because it is matching the actual string 'fullName' to '%a%' (I think).
SELECT * , DATE_FORMAT( `order_date` , '%d-%m-%Y' ) AS `date` , `fullName` FROM ( SELECT CONCAT( `customer_name` , ' ', `customer_surname` ) AS `fullName` , `customer_name` , `customer_surname` , `customer_id` , `customer_email` FROM `customer` ) AS `d` INNER JOIN `orders` ON `order_customer_id` = `customer_id` WHERE `fullName` LIKE '%a%' ORDER BY `customer_surname` ASC
Any help would be kindly appreciated.