sql select problem, from 2 tables

[mdwilliams]

I have (amongst other things) 2 tables: One for products, one for options.
Each option row has 3 fields, product (id of product from product table), title (type of option) and value.
There can be more than one row in the options table for each product as each product can have more then one option associated with it.

e.g:
Product Option Value
1 Sizes Small
1 Colour Red
2 Sizes Medium
2 Colour Red

In my example there are 2 products, each available in red but also in two different sizes.

I want to show a drop down list like this:

<option value=1>Sizes: Small Colours: Red</option>
<option value=2>Sizes: Medium Colours: Red</option>

So there is only one option per product, but all options for that product are shown.

I have tried platying around with GROUP BY, and DISTINCT but i never get the result I am after. Can anyone help me please?

Many thanks in advance - Mark

[nemonoman]

In this case you are storing what appears to be relatively free form data, but you want to present as structured.

This can be done with a single query but only if you are sure of that your data will be in a certain form, i.e., aloways 1 colour and one size per product as shown in your data

SELECT CONCAT(IF(title='Sizes',CONCAT('Sizes: ', value),,''), (IF(title='Colours',CONCAT('Colours: ', value),,''))
FROM options WHERE
product = 1

---

Alternate WHERE clause to select all product options:
FROM options
GROUP BY product

---

HOWEVER: if there is only one color and size per product, then you should simply store that data in a column on the product table, as a single attribulte of product.

IF there are multiple size and color attributes of options, but every size has a color and every color has a size, it would better to make your option table with these columns:

table.option:
Produtt color size
1 red small
1 blue small
1 red large
1 blue large

etc.

Or 2 tables

table.ProductSizeOption
product size
1 small
1 large
2 small
2 medium

table.ProductColorOption
product color
1 red
1 blue
2 blue
2 green

etc.

Your current design is likely going to drive you nuts in the long run.

---

By the way, this sort of IF clause is most typically used to do a kind of pivot table of financial results.

SELECT SUM(IF(MONTH(timestamp)=1, amount,0)),SUM(IF(MONTH(timestamp)=21, amount,0))
FROM ledger