Any SQL gurus? need help with this query

[jhdesign]

This is the query from whats_new.php

 

$random_product = tep_random_select("select products_id, products_image, products_tax_class_id, products_price from " . TABLE_PRODUCTS . " where products_status = '1' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)

 

I've installed a product short description contribution so I'd like to add it here too:

 

Table - products_description

Field added - products_info

 

I tried modifying the query myself but I get a products_id ambigious (sp?) error.

 

I beleive its because I need to match the products id in table products and products_description, just not sure how.

 

Any help is appreciated.

 

jacob

[kgt]

$random_product = tep_random_select("select products_id, products_image, products_tax_class_id, products_price from " . TABLE_PRODUCTS . " where products_status = '1' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)

 

Table - products_description

Field added - products_info

 

The error is because the field name products_id IS ambiguous to mysql. Do you want products.products_id, or products_description.products_id? You know they're basically the same thing, but mysql doesn't. So you need to use table identifiers like I just did.

 

$random_product = tep_random_select("select p.products_id, products_image, products_tax_class_id, products_price, products_description from " . TABLE_PRODUCTS . " as p left join " .TABLE_PRODUCTS_DESCRIPTION. " as pd on p.products_id=pd.products_id where products_status = '1' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)

[jhdesign]

Thank you. That worked perfectly.