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

I have the following query which returns a list of group ids that are attached to the product ids开发者_开发百科 specified in the IN clause.

SELECT t1.product_id, t2.group_id  
FROM table_1 t1  
LEFT JOIN table_2 t2  
ON t1.product_id = t2.product_id  
WHERE t1.product_id IN(1,2,3,4,5)  
AND t2.group_id = -1  
OR t2.group_id = 2

This is returning something like this:

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

The group_id will always have a default value of -1 but in some cases there may be more than one value (eg, product_id '1' has a group_id of '-1' and '2'). What I want to do is ignore the '-1' value when there is an alternative so I end up with this:

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

I've tried using a GROUP BY statement on the product_id but I just end up with the -1 values in the group_id column.

Please could somebody give me some pointers on how I might be able to do this!

SELECT t1.product_id, COALESCE(MAX(t2.group_id ),-1) AS group_id -- if there is no group id default to -1
FROM table_1 t1  
LEFT JOIN table_2 t2  
ON t1.product_id = t2.product_id  
WHERE t1.product_id IN(1,2,3,4,5)  
AND t2.group_id IN (-1, 2)
GROUP BY t1.product_id

SELECT t1.product_id, MAX(t2.group_id)  
FROM table_1 t1 , table_2 t2   
WHERE t1.product_id IN(1,2,3,4,5) 
AND t1.product_id = t2.product_id 
AND t2.group_id = -1  
OR t2.group_id = 2
GROUP BY (t1.product_id)

Try this. This will return 2 if both -1 and 2 are available. ie, I just select the max of the available group ids. Hope this is what you need here

you can use this sql:

SELECT t1.product_id, t2.group_id  
FROM table_1 t1  
LEFT JOIN (SELECT product_id, MAX(group_id) AS group_id
           FROM table_2
           WHERE group_id IN (-1,2)
           GROUP BY product_id) t2 ON t1.product_id = t2.product_id  
WHERE t1.product_id IN (1,2,3,4,5)

try this,

SELECT product_id, group_id= '-1' From products GROUP BY product_id HAVING COUNT(product_id) = 1 
UNION 
SELECT product_id, MAX(group_id) FROM products GROUP BY product_id HAVING COUNT(product_id) > 1