I have a database of orders and order details. Order table just holds general header level data and the details includes all the items on the order.
I want to group the details table and get a yes / no for if an item is on the order. I have the information below:
orders table:
ID, NAME, REP
1, Jon, Bill
2, Jarod, Jarod
detail table:
ORDER ID, PRODUCT NAME, PRODUCT TYPE
1, CUP, 1
1, PLATE, 3
1, BOWL, 3
2, CHAIR, 2
2, PLATE, 3
2, BOWL, 3
I want to know (yes/no) if an item type is on an order. I want results like this;
ID, NAME, REP, TYPE1, TYPE2, TYPE3
1, Jon, Bill, Yes, No, Yes
2, Jarod, Jarod, No, Yes, Yes
I started a query like so, but I do not know how to accomplish my results.
SELECT
orders,id,
orders.name,
CASE WHEN detail.type = 1 THEN "Yes" ELSE "No" END
CASE WHEN detail.type = 2 THEN "Yes" ELSE "No" END
CASE WHEN detail.type = 3 THEN "Yes" ELSE "No" END
FROM orders
LEFT JOIN details ON orders.id = details.id
GROUP BY details.id