Zubair Mushtaq Zubair Mushtaq - 4 months ago 14
SQL Question

How to get products from all categories but the product A must be from cateogry 1

I want to make a sql query to get all the products from all categories. The "product A" exists in more than one cateogries. I want product A only from "Category 1" and the all other products. Here, is my database table.

+------+------------+------------+
| id | category | product |
+------+------------+------------+
| 1 | 1 | Product A |
| 2 | 2 | Product B |
| 3 | 3 | Product C |
| 4 | 4 | Product A |
| 5 | 5 | Product F |
| 6 | 6 | Product D |
| 7 | 7 | Product A |
+------+------------+------------+


And the expected resut should be

+------+------------+------------+
| id | category | product |
+------+------------+------------+
| 1 | 1 | Product A |
| 2 | 2 | Product B |
| 3 | 3 | Product C |
| 4 | 5 | Product F |
| 5 | 6 | Product D |
+------+------------+------------+

Answer

You can use union

select category, product 
from my_table 
where product != 'Product A'
union 
select category, product 
from my_table 
where product =  'Product A'
and category = 1;

in your case

select * from videos 
where trim(submitted ) != 'video_artist' 
union 
select * from videos 
where Trim(submitted) = 'video_artist' 
and category = 1;