Search code examples
mysqlgroup-concat

MySQL String concatenation of Title and Name

I have 2 Tables Patient table and Title table

Patient Table 3 Fields

PatientTableID    FirstName PatTitleLookup 
1                  John      (Value Stored as integer)
2                  Mary      (Value Stored as integer)

Title Table 2 Fields

ID  Title
1      Mr.
2      Ms.

I want to concatenate Title and First Name

So I use this 

SELECT CONCAT(`PatientTable`.`PatTitleLookup`, '. ', `PatientTable`.`FirstName`)
FROM `PatientTable`
WHERE `PatientTable`.`PatientTableID` = 1

I get "1.John" instead of "Mr. John" "2.Mary" instead of "Ms. Mary"

Please advise I am sure my syntax is wrong

Solution

  • You need to join the titles table to get the values from it. Use an on for how the data should join.

    SELECT CONCAT(t.title, '. ', pt.FirstName)
FROM PatientTable as pt
join Title as t 
on pt.PatTitleLookup = t.id
WHERE pt.PatientTableID = 1

    I also have used aliases (as pt, as t) here for your tables, makes referencing shorter later in queries.