select from 3 tables - use join or union?

Question

I was wanting to select certain fields from 3 different tables and link by the id_product? i can't seem to find if a join or union should be used? I have tried the following already but had the error appear

SELECT id_product, price, wholesale_price, id_category_default, id_shop_default, description, description_short, link_rewrite, meta_description, meta_keywords, meta_title, name, id_shop, weight  
from ps_product, ps_product_lang, ps_product_attribute_shop 
where ps_product.id_product = ps_product_lang.id_product 
and ps_product_lang.id_product = ps_product_attribute_shop.id_product

Error Code: 1052. Column 'id_product' in field list is ambiguous 0.00031 sec

The tables and fields that I require are:

ps_product
----------
id_product,
price,
wholesale_price,
id_category_default,
id_shop_default.

ps_product_lang
---------------
id_product,
description,
description_short,
link_rewrite,
meta_description,
meta_keywords,
meta_title,
name.

ps_product_attribute_shop
-------------------------
id_product,
id_shop,
price,
weight.

any help would be greatly appreciated

Thanks for all your help.

I have managed to get what I was looking for using:

Select ps_product.id_product, ps_product.price, ps_product.wholesale_price, ps_product.id_category_default, ps_product.id_shop_default, ps_product_lang.description, ps_product_lang.description_short, ps_product_lang.link_rewrite, ps_product_lang.meta_description, ps_product_lang.meta_keywords, ps_product_lang.meta_title, ps_product_lang.name, ps_product_attribute_shop.id_shop, ps_product_attribute_shop.price, ps_product_attribute_shop.weight

FROM ps_product LEFT JOIN ps_product_lang on ps_product.id_product = ps_product_lang.id_product LEFT JOIN ps_product_attribute_shop ON ps_product.id_product = ps_product_attribute_shop.id_product

Probably a long way around but easier for me to follow

Please read the edit help about formatting code & look a the formatted text below the edit box. Also next time google your error message & read many answers. And read an introduction to SQL. — philipxy, Feb 13 '18 at 04:36

score 0 · Answer 1 · answered Feb 09 '18 at 12:53

column ambiguous error comes when it fails to understand which table you are trying to select from. In your select query create table aliases like ps_product as psp and then do select select psp.id_product, [[all columns with aliases]]

One way could be -> SELECT ps_product.id_product, ps_product_attribute_shop.price, ps_product_lang.description from ps_product, ps_product_lang, ps_product_attribute_shop where ps_product.id_product = ps_product_lang.id_product and ps_product_lang.id_product = ps_product_attribute_shop.id_product

Modify your query like this just make sure to feth columns from the table that has them and the one where you want to pick them from.

score 0 · Answer 2 · answered Feb 09 '18 at 12:57

Column ambiguity happens when there are two columns with the same name in tables which you are doing the join operation. In this case, you have to write the name of the table(or its alias) along with the column name explicitly.

 SELECT P.id_product, P.price, P.wholesale_price,P.id_category_default, P.id_shop_default,
    PL.[description], PL.description_short, PL.link_rewrite, PL.meta_description, PL.meta_keywords, PL.meta_title, PL.name,
    PA.id_shop, PA.price, PA.[weight]
    FROM ps_product P
    LEFT JOIN ps_product_lang PL
    P.id_product = PL.id_product
    LEFT JOIN ps_product_attribute_shop PA
    ON P.id_product = PA.id_product

select from 3 tables - use join or union?

2 Answers2