mysql error in my query

Question

i have to check in my products i am selling (mostly gaming consoles and games)

i want to see which products has which categories and this is my query:

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 removed the category,

I don't want to see the product without categories?

Answer 1

The LEFT JOIN command is used to combines null matching rows which are stored in related tables In order to join these tables, the join table require a common field (commonly called foreign key) from the left table. This type of join requires 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 the INNER JOIN:

The JOIN or INNER JOIN command is used to combines non-null matching rows which are stored in related tables In order to join these tables, the join table require a common field (commonly called foreign key) from the left table. This type of join requires keywords ON or USING.

Example:

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

Now, I would recommend to add a flag for inactive or active product, this way you don't need to remove the categories for a product if it's inactive. This way, if you want to re-activate it, simply turn the flag back to 1 or whatever flag you use.

Example:

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