[El Forum]

[eluser]iamzozo[/eluser]
Hi!

I've created a query

Code:

$this->db->select('a.id, a.title, b.child_title');
$this->db->from('a');
$this->db->where('a.id', $id);
$this->db->join('b','a.id = b.parent');
$this->db->get();

It's working fine, but i would like the result show up something like this:
[id] => 1,
[title] => Lorem ipsum,
[child_titles] => Array()....

Now it gives in each object the a.title (as join work).

- I did it with two query, but is it possible to create this with one query?
- Has the two query (much) slower performance (one row query, one list)? - "a" table has got aprox 30K rows, "b" has got aprox. 600K.

Thanks!

[El Forum]

[eluser]boltsabre[/eluser]
You want to do a right join (it will return all rows of data from table b where you join "ON" criteria matches even if there is no match in table a).

http://www.w3schools.com/sql/sql_join_right.asp

Not sure if active records can do a right join, you'll have to investigate that yourself, but otherwise, native sql isn't that hard to code!

[El Forum]

[eluser]iamzozo[/eluser]
I want to get only that rows which has got this "parent" row.
So like on a blog post detail page, and show the comments for it. Now my join it's working well, but i have to separate the result and rebuild an assoc. array. (I'm not lazy to create it, i just would like to know if there is any solution with one query , if there is no solution for this, or which is worth it, i will go on with 2 query)

[El Forum]

[eluser]boltsabre[/eluser]
Oh right, oppps, sorry, completely dismiss my above comment, a right join will return all rows from table b, so long as it has 'b.parent'... don't know what I was thinking, brain fade, long day at work here.

So let me get this right... want to get a blog post and its details (table a) AND all comments associated to that particular post (table b)?

If that's the case, run two queries, one to get the blog details, another to get its comments.

[El Forum]

[eluser]iamzozo[/eluser]
Yes, that's it! Then i will run 2 queries.
Thanks!

[El Forum]

[eluser]boltsabre[/eluser]
no worries, sorry it took so long to get there ;-)