How do I join a table and change the names of the returned columns?

I've got a MS SQL 2008 table called "fruits" that looks like this:

id   name           category_id
--   ------------   -----------
 1   Apples                   4
 2   Bananas                  6
 3   Crackers                 6

and another table called "categories" with:

id   category_name
--   -------------
 4   Foo
 6   Bar

How do I produce a query that will return results that look like:

fruit_name  fruit_category_name
----------  -------------------
Apples      Foo
Bananas     Bar
Crackers    Bar

(The columns must be exa开发者_如何学JAVActly that name because I'm trying to write a CSV.)

SELECT f.name AS fruit_name, c.category_name AS fruit_category_name
FROM fruits AS f
INNER JOIN categories AS c ON c.id = f.category_id

Try this:

SELECT f.name AS fruit_name, c.category_name AS fruit_category_name
FROM fruits f INNER JOIN categories c
ON f.id = c.id
ORDER BY f.name

select name as fruit_name, category_name as fruit_category_name
from fruits
inner join categories on (fruits.category_id = categories.id)
order by fruit_name

SELECT fruits.name AS fruit_name, categories.category_name AS fruit_category_name FROM fruits INNER JOIN categories ON (fruits.category_id = categories.category_name)

Something like this?:

SELECT
    f.name AS fruit_name,
    c.category_name AS fruit_category_name
FROM
    fruits f
JOIN
    categories c ON c.id = f.category_id
ORDER BY
    fruit_name DESC

you can rename a column in one of two ways:

SELECT
    ColumnName AS DisplayName
    FROM ...

or

SELECT
    DisplayName = ColumnName
    FROM ...

Remember, use [ and ] if you need to use special characters (like space) within the display name:

SELECT
    ColumnName AS [Display Name]
    FROM ...

or

SELECT
    [Display Name] = ColumnName
    FROM ...

You can do your join like this:

SELECT 
    f.name AS fruit_name
        ,c.category_name AS fruit_category_name
    FROM Fruits             f
    INNER JOIN Categories   c ON c.id=f.category_id