Query to get the data in related table

Question

There is two table : admin , news

The structure is like this:

admin
-----
id
name

news
-----
id
createBy
updateBy

currently the createBy and updateBy stores the admin id , I would like to get the admin name of createBy and updateBy, how to achieve that? Thanks

Here is what I used in codeigniter

$this->db->select('*');
        $this->db->from('news,admin');
        $this->db->where('id', $id);
        $this->db->where('admin.id', 'news.updateBy');

Anyway the sql query is welcome, I will convert it to codeigniter syntax . Thanks for helping

Answer 1

This is a query that will pull a particular news ID and get both the created by and updated by values from your admins table:

select
    adu.id sd as updateID,
    adu.name as updateName,
    adc.id as createID,
    adc.name as createName
from
    news ns
        left outer join admin adu
            on ns.updateBy=adu.id
        left outer join admin adc
            on ns.createBy=adc.id
where
    news.id=1

I have joined the admins table twice - as the admin updating may be different from the admin creating - and an inner join wouldn't work if either field was empty. Doing this will also allow you to pick which you want (creating or updating) but still have them in the same row of data.

If this is what you were looking for, it is a straightforward enough query and you might really benefit from reading this question and answer I put together to explain queries like this in a lot more detail. It shows and explains in detail inner and outer joins, data grouping and aggregation.

Edit: I have added column aliases to the query which means you can pick which one you want. The article I wrote and linked to also explained that in detail :)