Setting MySQL column names

Say I have these tables:

peop开发者_JAVA技巧le(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?

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.

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

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.