How do you do a join? (SOLVED)

[eluser]tomdelonge[/eluser]
I've never used a join before, so I'm not quite sure if my situation is right for it.

Say I have a product and I need to retrieve the name of the category it is placed in. I only store the category id in the product table.

Product Table
id
name
category_id

Category Table
id
name

So, as you can see, I need to retrieve the name of the category associated with the product id.

What's the best way to do this? Do I use a join? Or not at all, and just take the long way which would be way more complicated, as I'm retrieving several products.

[eluser]jedd[/eluser]
I can recommend the [url="http://dev.mysql.com/doc/refman/5.1/en/"]MySQL Reference Manual[/url] but there's plenty of tutorial sites out there .. here [url="http://lmgtfy.com/?q=sql+join+tutorial"]let me give you a hint[/url].

Oh, btw:

Code:

SELECT name, category.name AS category_name
FROM product
LEFT JOIN category ON category.id=product.category_id
WHERE product.id=$x

[eluser]tomdelonge[/eluser]
Sorry, I'm more talking about how to refer to them after. It looks like when I refer to $row->name, it uses the category name, but then how do I refer to the product name? (This is after I get the result set).

You answered my question. Thanks. (I hadn't used "AS")

[eluser]kgill[/eluser]
Take a second look at jedd's example code, use aliases if your tables have the same named columns or we could start a holy war here mention longer named columns can also solve the problem (e.g. cat_name, cat_id, prod_name, prod_id) - either way works.

edit: heh looks like you already took the 2nd look