Need help with product query

[chieffan]

I ma creating a query that will give me the product name within a specific category.

 

I am using Products, products_to_categories, and product_description.

 

I can't seem to query correctly. What I really need is the product name, the product status and category id. I will later add a where statement for the category.

 

This is the Query

 

SELECT *

FROM ((products

INNER JOIN products_to_categories ON products_to_categories.products_id=products.products_id)

INNER JOIN products_description ON products_description.products_id=products_to_categories.products_id)

 

 

Any help is appreciated.

[FalseDawn]

You were fairly close, this should do it:

 

SELECT P.products_id, PD.products_name, P.products_status, C.categories_id

FROM

(((products P INNER JOIN products_description PD ON P.products_id=PD.products_id)

INNER JOIN products_to_categories PTC ON P.products_id=PTC.products_id)

INNER JOIN categories C ON PTC.categories_id=C.categories_id)

WHERE PD.language_id=1

 

 

If you leave the WHERE off, you'll get multiple records for each language. Assuming 1 = Engligh

[chieffan]

Thank you very much for your help. I appreciate it. I modified the query slightly and now it works perfect. English is the only language I have setup. Getting the product status was key.

 

SELECT P.products_id, PD.products_name, P.products_status, C.categories_id

FROM

(((products P INNER JOIN products_description PD ON P.products_id=PD.products_id)

INNER JOIN products_to_categories PTC ON P.products_id=PTC.products_id)

INNER JOIN categories C ON PTC.categories_id=C.categories_id)

WHERE C.categories_id=21 AND P.products_status=1