CODEWITHSUNDEEP
mysqlsql
FlySpaceAge
FlySpaceAge

Reputation: 95

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:

Upvotes: 0

Views: 318

Answers (3)

Hogan
Hogan

Reputation: 70529

dave has the heart of this problem, but I expect you also will want to deal with nulls. I think the final code you want will look like this:

COALESCE(CONCAT(e.last_name,', ',e.first_name),e.last_name,e.first_name,'') AS guide_name,
COALESCE(CONCAT(g.last_name,', ',g.first_name),g.last_name,g.first_name,'') AS guest_name,

Upvotes: 0

Stu Cartwright
Stu Cartwright

Reputation: 376

With + in the concat, mysql thinks you want them treated as numbers. Perhaps you're confusing it with javascript?

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat

Upvotes: 0

dave
dave

Reputation: 64667

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.

Upvotes: 1

Related Questions