Matching multiple rows in a MySQL database A custom built eCommerce store has the following two tables:

orders

| order_id | user_id | amount | shipping_info | date_time           |
|----------|---------|--------|---------------|---------------------|
| 222      | 7       | 999    | XXX           | 2017-07-20 05:54:45 |
| 333      | 5       | 2599   | XXX           | 2017-06-18 22:34:25 |
| 444      | 9       | 1859   | XXX           | 2017-07-01 15:07:54 |


ordered_products

| order_id | product_id | amount | quantity | product_type |
|----------|------------|--------|----------|--------------|
| 222      | 6546       | 53     | 2        | A            |
| 222      | 8979       | 85.99  | 1        | B            |
| 444      | 9879       | 87     | 2        | A            |
| 444      | 4756       | 25     | 2        | A            |
| 333      | 3215       | 129    | 2        | B            |


I want to list order ids which


contain product_type "A" but NOT "B"
contain product_type "B" but NOT "A"
contains both product_type "A" and "B"


I can't find a way to do this without using multiple queries and PHP loops. Is this possible using only SQL queries?
This is usually done using conditional aggregation plus some applied logic:

select order_id,
   case max(case when product_type = 'A' then 1 else 0 end) +
        max(case when product_type = 'B' then 2 else 0 end)
      when 1 then 'A not B'
      when 2 then 'B not A'
      when 3 then 'A and B'
   end
from mytable
where product_type in ('A','B')
group by order_id