use test;

SELECT a.userid,d.order_type, SUM(IF(a.order_type = d.order_type, price, 0)) AS orderPrice FROM a
JOIN
(SELECT DISTINCT a.userid,b.order_type FROM a CROSS JOIN b)d WHERE a.userid= d.userid
GROUP BY a.userid, d.order_type
ORDER BY a.userid, d.order_type;