CODEWITHSUNDEEP
mysqlgetopencart
Bogdan Onyshenko
Bogdan Onyshenko

Reputation: 97

Joins in mysql what to do

Well I wrote a query and get an error:

Column 'product_id' in field list is ambiguous Error No: 1052

I need to select the same id from 2 tables and compare them by price here is query I wrote:

$product_sql_test1 = $this->db->query("SELECT `product_id` and 'price'    FROM `" . DB_PREFIX . "product_to_category`INNER JOIN (oc_product)ON oc_product.product_id=prdoduct_to_category.product_id WHERE product_to_category.id=product.id and  price >150 and `category_id`='".$product_info['related_kv4nt_id_1']."'  GROUP BY `product_id` ORDER BY rand() LIMIT 0,10");

Where could be an error and how to fix it? Sorry if the question is too simple.

Upvotes: 1

Views: 76

Answers (5)

Jay Gilford
Jay Gilford

Reputation: 15151

The correct way to do this is

$product_sql_test1 = $this->db->query("SELECT `p`.`product_id`, `p`.`price` FROM `" . DB_PREFIX . "product_to_category` `p2c` LEFT JOIN `" . DB_PREFIX . "product` `p` ON `p`.`product_id` = `p2c`.`product_id` WHERE `p`.`price` > 150 and `p2c`.`category_id`='" . (int) $product_info['related_kv4nt_id_1'] . "' GROUP BY `p`.`product_id` ORDER BY rand() LIMIT 0,10");

You should also consider formatting your SQL to make it easier to read as well

Upvotes: 0

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68476

Use an Alias name for tables and columns.

Access it like oc.product_id (for column) and oc_product oc(for table)

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You have to mention the table name while selecting the product_id because many tables have this column and mysql is confused to select the column from which table

$product_sql_test1 = $this->db->query("SELECT oc_product.`product_id` and 'price'    
FROM `" . DB_PREFIX . "product_to_category`INNER JOIN (oc_product)ON 
oc_product.product_id=prdoduct_to_category.product_id WHERE product_to_category.id=product.id and  price >150 and prdoduct_to_category.`category_id`='".$product_info['related_kv4nt_id_1']."' 
GROUP BY oc_product.`product_id` ORDER BY rand() LIMIT 0,10");

Upvotes: 2

kevinm
kevinm

Reputation: 475

Both in your Select and your Group By, you need to use your alias table names along with your column names because the same column is present in multiple tables and thus there's a confusion as to which one to use for the result.

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37566

Use the full name:

Tablename.ColumnName

For Example in the GROUP BY part it is not clear which product_id you mean.

Upvotes: 0

Related Questions