I'm looking to write a query in SQL Server that does the following as efficiently as possible:
I have the following three tables - People, PeoplesNames, Names
The table People holds a unique person.
The table PeoplesNames holds the primary key of a person with the primary key of a name, along with the type of name it is (first name, surname etc.)
The table Names holds a unique name.
A person would have more than one name - for example, a first name, middle name and a surname.
I am currently using a JOIN statement to get a person and their names.
This JOIN statement, however, will return more than one row for each person - the person's Id will stay the same in three or four rows (however many names they have) but the name will change.
I am currently doing the processing and merging of the names in my program, not the SQL.
Is there any way with SQL to return, for example, first name, middle name, surname all in one row in different fields?