Need help refining query with a join to retrieve specific records

Databases
1

I looked for help with this in late December but perhaps was not framing the question correctly.

I’ve used this query to pull a list of products from a Zencart database:


$query = "
SELECT * FROM products 
LEFT JOIN products_description
ON 
products.products_id = products_description.products_id
GROUP BY products.products_id
order by master_categories_id ASC
";

I need to somehow refine my query to select only products with a specific master_categories_id, in order to display items only from a specific category.

I tried this:

$query = "
SELECT * FROM products where master_categories_id = 3
UNION
SELECT * FROM products 
LEFT JOIN products_description
ON 
products.products_id = products_description.products_id
GROUP BY products.products_id
";

But I keep getting the following error: The used SELECT statements have a different number of columns. Is there a different way to construct this query that would work better (ie some sort of workaround for the differing number of columns)?

Thanks for your help.

2

I ended up with this and it seems to be working.

$query = "SELECT products.products_id,products.products_image,products.products_price,products.master_categories_id,products_description.products_description
FROM products LEFT JOIN products_description ON
products.products_id = products_description.products_id 
where master_categories_id = 18
order by master_categories_id ASC"
;
3

you don’t actually need the ORDER BY clause :slight_smile:

4

Oh, you’re right. Thanks Rudy.