Jamal Kendrick Jamal Kendrick - 3 months ago 5
MySQL Question

Concatenting first and last name while Joining to another table with an ID

I am trying to solve an issue with joining last and first names that are identified using id's in another table. My code is producing the correct fields, but the Guide_Name and Guest_Name columns show all/only 0 (zero). Here is my code:

use www;
SELECT
d.destination_name,
tt.trip_type_name,
t.trip_number,
t.trip_date,
CONCAT(e.last_name + ', ' + e.first_name) AS guide_name,
CONCAT(g.last_name + ', ' + g.first_name) AS guest_name,
ex.exp_name AS guest_experience,
g.age AS guest_age,
g.weight AS guest_weight,
g.swimmer AS guest_is_swimmer,
g.mobile_phone AS guest_mobile_phone
FROM
trip_type tt
JOIN
trips t ON tt.trip_type_code = t.trip_type_code
JOIN
destination d ON t.destination_code = d.destination_code
JOIN
reservation r ON t.trip_number = r.trip_number
JOIN
guests g ON r.guest_id = g.guest_id
JOIN
experience ex ON ex.exp_code = g.exp_code
JOIN
employees e ON t.guide_employee_id = e.employee_id
ORDER BY d.destination_name , tt.trip_type_name , t.trip_date , g.last_name , e.employee_id


And here is the EER diagram:

And here is the EER diagram:

Answer

CONCAT should just be a comma separated list of strings, so I would first change

CONCAT(e.last_name + ', ' + e.first_name)

to

CONCAT(e.last_name, ', ', e.first_name)

and see if that helps.