Can I do this with just SQL?

Question

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)?

Answer 1

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 *.

Answer 2

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

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