Active records and complex join query

[eluser]softsaurus[/eluser]
I hope somebody here can help me out with this issue I'm having, I would be forever greatful!
I'm having great difficulty getting my query to run right.
I'm trying to join the following tables:

Code:

- entries (id, title, body...)
- images (id, title, description, src...)
- images_entries (id, image_id, entry_id)(which of course contains the corresponding relationships).

What I'd like my query to do is be able to get the fields contained in 'images' and link them to the entry on my 'blog' by using the relational table.

It's also good to know that my knowledge on relational databases and using joins is quite slim.. have been studying these darn queries for days now but I just can't wrap my head around this one...

This is what I got so far..

Code:

SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`body`, `ImgEnt`.`id`, `ImgEnt`.`entry_id`, `ImgEnt`.`image_id` FROM `entries` AS `Entry` JOIN `images_entries` AS `ImgEnt` ON (`ImgEnt`.`image_id` = 1 AND `ImgEnt`.`image_id` = `Entry`.`id`)

This however results in the joining of the images_entries table but not being able to call upon the images table and it's content. So my guess is that another join should be made somewhere.. but I'm clueless as to where and how..

I also translated the query above to an 'active record' CI style query but without having the knowledge as to where and how to join the second table that's pretty useless..
But for the record, this is what I came up with:

Code:

$this->db->select('entries.id, entries.title, entries.body');
$this->db->from('entries');
$this->db->join('images_entries', 'entries.id = images_entries.entry_id');
$q = $this->db->get();

If one of you could help me out that would be awesome

Thanks in advance guys, this thing has been bugging me for months now! FAIL

[eluser]seanloving[/eluser]
[quote author="softsaurus" date="1274377216"]
What I'd like my query to do is be able to get the fields contained in 'images' and link them to the entry on my 'blog' by using the relational table.
[/quote]

I'm learning too, so you should ask for another opinion, but I think you might try something like this:

Code:

$this->db->select('entries.id, entries.title, entries.body, images.title AS image_title, images.description, images.src');
$this->db->from('entries');
$this->db->where('entries.id' = $entry_id); // the calling function supplies $entry_id
$this->db->join('images_entries', 'entries.id = images_entries.entry_id', 'left');
$this->db->join('images', 'images_entries.image_id = images.id', 'left');
$q = $this->db->get();

good luck
--Sean Loving

[eluser]Mr. Pickle[/eluser]
@seanloving, I see nothing wrong with your approach.
of course

Code:

$this->db->where('entries.id' = $entry_id); // the calling function supplies $entry_id

should only be done for having the detail page of an entry.

Not implementing this rule will get all entries (also with the requested relation)

[eluser]softsaurus[/eluser]
Hi Guys,

Thanks for your quick response, thats awesome!
As for now I haven't tried out your suggestions yet but tonight I'll be continuing on my project and I'll make sure to let you know when and if it'll work.

Thanks for the suggestions, I'll soon update on the matter.

[eluser]softsaurus[/eluser]
Hi there guys, sorry for the late response, I've been away much lately.
But nothing sucks more than an unresolved topic so let me clarify.

@seanloving your approach is totally valid!
Thank you both for your suggestions and sollution, it's working fine right now.
Can't link you to my project for it's still undergoing development, but I thank you!

Again, sorry for keeping you waiting so long.