mysql - SQL Query The Will Tell If Any Part On An Order Is Non-Stock -

i don't know best way word question, had hard time searching solution. have query pulls orders on given day. need know orders have items on inventory = 'n'. i'm not looking find orders of items inventory ='n'. if there @ least 1 of them, i'd 'y' in separate column.

select  a.ordernum,  c.inventory, case when c.inventory = 'n' 'y' else 'n' end nonstock orders inner join orditems b on a.ordnum = b.ordnum inner join items  c on b.code = c.code a.orderdate = '7/24/2017'  group a.ordernum, c.inventory 

this original code came with. know isn't correct. need similar (or different, don't know) nonstock column y long of items order have c.inventory = 'n'. going in right direction @ all? guidance appreciated.

sample data:

a.ordernum  b.itemnum  c.inventory 123         3          y 123         4          n 123         5          y 124         6          y 124         9          n 124         8          y 125         11         y 125         13         y 125         15         y 

desired output:

a.ordernum nonstock 123        y 124        y 125        n 

since orders 123 , 124 have item on c.inventory = 'n' triggered nonstock on desired output. order 125 has 3 items c.inventory = 'y' 'n' nonstock.

that's correct , need use case expression purpose don't see need using group by @ all. mean line group a.ordernum, c.inventory. query should

select ordernum, max(nonstock) nonstock ( select  a.ordernum,  c.inventory, case when c.inventory = 'n' 'y' else 'n' end nonstock orders inner join orditems b on a.ordnum = b.ordnum inner join items  c on b.code = c.code a.orderdate = '7/24/2017' ) xxx group ordernum;