Setting MySQL column names

Question

Say I have these tables:

people(id, name), cars(person_id, car)

and this query:

SELECT c.car
FROM people as p, cars as c
WHERE c.person_id = p.id
  AND p.id = 3

I want the c.car column to take its name from the name field in the people table, like this (invalid SQL, just to illustrate):

SELECT c.car AS(SELECT name FROM people WHERE id = 3)

How do I do that?

Answer 1

I'm not sure I understand completely. The c.car field refers quite specifically to the car field in the cars table.

To refer to the name field in the people table, use p.name

SELECT c.car, p.name
FROM people as p, cars as c
WHERE c.person_id = p.id
  AND p.id = 3

Answer 2

You can't in general. What would be the column name if it returned multiple different results? If you know it can only return 1 result you would need to look that up then use dynamic SQL to alias the column in a separate query.

Answer 3

The alias must be hardcoded in your query. You can't change an alias based on data values.

You'll have to query both the car and the person's name (as shown by @VoteyDisciple's answer) and match them up in your application code after you've fetched them.