sql - MySQL: How to select a preferred value from multiple rows (and fall back to a default)? -

i have following query returns list of group ids attached product ids specified in in clause.

select t1.product_id, t2.group_id   table_1 t1   left join table_2 t2   on t1.product_id = t2.product_id   t1.product_id in(1,2,3,4,5)   , t2.group_id = -1   or t2.group_id = 2 

this returning this:

+------------+----------+ | product_id | group_id | +------------+----------+ | 1          | -1       | | 1          |  2       | | 2          | -1       | | 3          | -1       | | 4          | -1       | | 5          | -1       | +------------+----------+ 

the group_id have default value of -1 in cases there may more 1 value (eg, product_id '1' has group_id of '-1' , '2'). want ignore '-1' value when there alternative end this:

+------------+----------+ | product_id | group_id | +------------+----------+ | 1          |  2       | | 2          | -1       | | 3          | -1       | | 4          | -1       | | 5          | -1       | +------------+----------+ 

i've tried using group statement on product_id end -1 values in group_id column.

please give me pointers on how might able this!

select t1.product_id, coalesce(max(t2.group_id ),-1) group_id -- if there no group id default -1 table_1 t1   left join table_2 t2   on t1.product_id = t2.product_id   t1.product_id in(1,2,3,4,5)   , t2.group_id in (-1, 2) group t1.product_id