INNER JOIN query problem

[lilbadger25]

I have acquired an already structured database for a page and they (as an afterthought) decided they would now like a CMS that would allow them to add products.

 

There are two databases:

 

products includes PID (id, obviously), prod_catID (the id of a category in the second table), prod_name, description, tech_info1, image, available, status

 

product_cat includes ID (matching the prod_catID in first table) and cat_name (the category name)

 

I have never joined tables before. I don't have time to restructure the db (pages have already been written) but I see the benefits of having the two tables.

 

For each row, I need PID, the category name (cat_name) associated with the number in the prod_catID field, description, tech_info1, image, available, and status

 

A friend of mine suggested using something like this:

	$query = "SELECT products.*, prod_catID as cat_name from products INNER JOIN product_cat ON products.prod_catID = product_cat.prod_catID";

 

but it's erroring Column: 'prod_catID' in field list is ambiguous

[franzy_pan]

its a long shot but have you tried individually specifying the products table fields rather than products.*?

 

Otherwise it might be because you are not being specific enough with the field name - what is the exact error?

[fenway]

Just use a table alias and the ambiguity will disappear.

[lilbadger25]

I have never even seen a table alias before it was suggested. I took a look online for some tutorials and checked for the syntax in some of my books. Although I understand the theory, I am so confused as to how to write the select statement for this case. Additionally, do the aliases now become the variables?

[fenway]

The posted code is using table prefixes/aliases in the where clause, but not in the select column list.

[lilbadger25]

ok, thanks, fenway...that gives me some direction.