Title: Join where one row should be duplicated
Tags: mysql;join
Question: Struggling to come up with a mysql only solution to this.
Simplified setup:
Two tables:
Table A
id | value | price
1 | 2     | 5.95
2 | 5     | 7.95
3 | 30    | 8.95
Table B:
color | min_value | max_value
green  |     1     |  3
blue   |     3     | 10
red    |    10     | 35
orange |    25     | 50
Based on the definitions from table B, i would like to join the color column onto the data from table A.  I need to know, in this example, the average price associated with all the items in table A by color.  However the definitions can be overlapping (i.e. id 3 is both red and orange).
I know I can use python (in this project's case) to recursively go through each color and pull out the matching values and then the average price, but i would love to simplify this by doing it mysql.
Thanks for any help/guidance!
Comment: One for each.  So id #3 would exist in both the groupings for red and orange.

Comment: So how should it count if something is of multiple colors? For each color once? Or only `1/n` for each of the n matching colors?

Here is the accepted answer: You don't need to do anything special, the default case in SQL is that if one row from a table matches two rows in the other, you'll get two rows in the result.
SELECT color, avg(price)
FROM A, B
WHERE A.value BETWEEN B.min_value AND B.max_value
GROUP BY color
Comment for this answer: Thank you.  I think i had some superfluous groupings that was causing problems and I assumed it was just not giving me the multiple rows I needed.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:09/02/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><join>', 'Title': 'Join where one row should be duplicated', 'ViewCount': 25, 'id': 18581360, 'user_AboutMe': None, 'user_AccountId': 1811734.0, 'user_DisplayName': 'dgfin', 'user_Reputation': 3.0, 'user_Views': 23.0, 'user_id': 1647645.0}}