You have two hypothetical tables:
[id] [item] [amount] [cost]
1 hat 20 10
2 shoe 7 45
3 belt 2 25
TABLE 2 (many to many)
[ITEM] [AMOUNT] [COST] [AVAILABLE COLORS]
hat 20 10 blue, red, yellow
shoe 7 45 black, white
If you are working with MySQL, the
GROUP_CONCAT function might interest you.
See for instance this question : Can I concatenate multiple MySQL rows into one field?
Else, the "brute force" solution you described is generally the one that's used -- a possible optimization being to do only 1 query to get lines from the second table that correspond to all lines from the first table at once.
For instance, you'd :
select * from table_2 where item in ('hat', 'shoe', 'belt')
With this solution, you'll do a bit more work on the PHP side, but only 2 queries -- instead of 1 + (number of lines returned from the first query) ; which is generally much better, when you have lots of lines returned from the first query.