MySQL looking table query

[tibberous]

I have two tables, one named items and the other named categories. Categories has two columns, an id and a name.

 

Ex:

 

id name

1  Action

2  Classic

3  Adventure

 

The items table has a field called category. This is a number, that represents the id in the other table. So, if the category value was 2, the category would be Classic.

 

If I have an item in the items table, and want to get the text name of the category, how would I make the query? Basically I am doing a lookup table, but I forget how. Can anyone help me out? If I'm not being clear I'll explain better - it is kind of hard to say

[pocobueno1388]

SELECT i.item, c.name
FROM items i
LEFT JOIN categories
ON i.category = c.id

[beebum]

You could also do:

 

SELECT items.item as item, category.name as category

FROM items, categories

where items.category = category.id;

 

I'm not sure but I think this only became available in 5.0.

[pocobueno1388]

You could also do:

 

SELECT items.item as item, category.name as category

FROM items, categories

where items.category = category.id;

 

I'm not sure but I think this only became available in 5.0.

 

That method would work, but using joins is more efficient. Also, there is no reason to alias the field names as you did in that query.