Mysql error in my query

Question

I need to check out my products that I sell (mainly game consoles and games).

I want to see which products have which categories, and this is my request:

select * From products left join products_categories on (product_id=id) ; +------+------+------------+-------------+---------- | id | name | product_id | category_id | and more +------+------+------------+-------------+---------- | 4 | Xbox | 4 | 2 | | 5 | PS3 | 5 | 2 | | 7 | BAD | NULL | NULL | etc... +------+------+------------+-------------+--------- 

here I have a product (# 7 - BAD) that I don’t want to see, since I deleted the category,

I do not want to see a product without categories?

Answer 1

The LEFT JOIN command is used to join zero matching rows that are stored in linked tables. To join these tables, the connection table requires a common field (usually called a foreign key) on the left of the table. This type of connection requires the keywords ON or USING.

Example:

 SELECT * From products LEFT JOIN products_categories ON (product_id=id) WHERE product_id IS NOT NULL; 

Or you can use INNER JOIN:

The JOIN or INNER JOIN command is used to join non-empty row matches that are stored in related tables. To join these tables, the connection table requires a common field (usually called a foreign key) from the left table. This type of connection requires the keywords ON or USING.

Example:

 SELECT * From products INNER JOIN products_categories ON (product_id=id); 

Now I would recommend adding a flag for an inactive or active product, so you do not need to remove categories for the product if it is inactive. Thus, if you want to reactivate it, simply return flag 1 or any other flag that you use.

Example:

 SELECT * FROM products INNER JOIN products_categories ON (product_id=id) WHERE products.is_active = 1;