Help needed with one to many selects

[eluser]beerguide_au[/eluser]
I am struggling to get my head around how to do some one to many relationships that I have in my controllers and views.

Essentially I have two tables, 'beers' and 'reviews'. A beer can have one, or many reviews but a single review can only be associated with a single beer.

The problem I am having is that when I retrieve a list of all reviews I need to include the beer details and it looks like I'm pulling back all beers in my results rather than just the reviews with the beers that apply included. So, for example, instead of getting back 3 reviews for 10 beers, I get 10 rows back instead.

I am using the following code in my model:

Code:

$this->db->join('beers', 'beers.beer_id = beer_reviews.beer_id');
$query = $this->db->get('beer_reviews', $limit, $offset);
return $query->result();

I'm thinking I need to loop over the reviews, then grab the beers details in my controller and then pass both the array of reviews and the beer arrays to the view - but I am really struggling with this. Am I even on the right track?

Has anyone got any ideas as I'm really stuck and would appreciate the assistance.

I am not using any additional data mapping/ORM plugins etc.. it is a default Codeigniter installation.

Thanks in advance.

[eluser]Future Webs[/eluser]
should be a straight forward join but you should make sure that you use the left join to ensure that you get results only where there is a join and not everything

Code:

$this->db->join('beers', 'beers.beer_id = beer_reviews.beer_id', 'left');
$query = $this->db->get('beer_reviews');
return $query->result();

[eluser]toopay[/eluser]
Can i get some beer,then? i will wrote review for it...

[eluser]beerguide_au[/eluser]
[quote author="fwebs" date="1296256164"]should be a straight forward join but you should make sure that you use the left join to ensure that you get results only where there is a join and not everything
[/quote]

Yep, you're right. I have data issues whereby I have multiple beers of the same ID. Such a simple error that was staring me in the face for so long.

Thanks for your help.