Can I do this with just SQL?

At the moment I have two tables, products and options.

Products contains

1. id
2. title
3. description

Options contains

1. id
2. product_id
3. sku
4. title

Sample data may be:

Products

id: 1

title: 'test'

description: 'my description'

Options

id: 1

product_id: 1

sku: 1001

title: 'red'

id: 2

product_id: 1

sku: 1002

title: '开发者_运维问答blue'

I need to display each item, with each different option. At the moment, I select the rows in products and iterate through them, and for each one select the appropriate rows from options. I then create an array, similar to:

[product_title] = 'test';  
[description]   = 'my description';  
[options][]     = 1, 1001, 'red';  
[options][]     = 2, 1002, 'blue';

Is there a better way to do this with just sql (I'm using codeigniter, and would ideally like to use the Active Record class)?

Start with

SELECT * FROM products INNER JOIN options ON product.id = options.product_id

and work from there.

The example only works for products with at least one option available (which is required by your design, otherwise you have no place for the SKU). But if some products don't have any options at all, you want:

SELECT * FROM products LEFT JOIN options ON product.id = options.product_id

Finally, it's considered good practice to list the actual columns you want to get back instead of SELECT *.

You need to join products and options. Try this query:

select *
from products, options
where products.id = options.product_id

select * from Products a,option b where a.id = b.product_id;

or

you can use

select * from option where product_id = (select id from Products )