mysql - get unique color_id which stock's quantity is more than order's quantity -

mysql - get unique color_id which stock's quantity is more than order's quantity -

i want color_id product's stock(quantity) available.

table - stock

id | product_id | color_id | quantity

table - order

id | client_id | stock_id | quantity

i want unique color_id stock stock's quantity more order's quantity , on particular product_id. if stock id doesn't exist in order table order's quantity takes 0;

table order.stock_id foreign key of table stock.id

i tried below query

select * stock s, order o s.id = o.stock_id , (s.quantity-o.quantity)>0

if o.quantity doesn't exist should take 0

it hard tell text asking. but, if want treat "missing" orders having 0 quantity, want left outer join:

select * stock s left bring together order o on s.id = o.stock_id s.quantity > coalesce(o.quantity, 0);

i'm not sure has "color", sample query has no mention of color either.

mysql sql