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

score 5 · Accepted Answer · edited May 23 '17 at 12:22

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

sorry if I try to select * , then how can I get the adu.name and adc.name? — user782104, Jun 09 '14 at 06:18

Fabricator · Answer 2 · 2014-06-09T06:25:42.127

0

mysql statement:

select a.id, b.name creator, c.name updater
from news a
join admin b on a.createBy = b.id
join admin c on a.updateBy = b.id
where a.id = $id;

edited Jun 09 '14 at 06:25

answered Jun 09 '14 at 06:16

Fabricator

12,722
2
27
40

The admin ID isn't the id of the news table. How will this work? – Fluffeh Jun 09 '14 at 06:22
1

@Fluffeh, thanks. I completely misunderstood the question. it's corrected – Fabricator Jun 09 '14 at 06:26

score 0 · Answer 3 · answered Jun 09 '14 at 06:17

0

You can try following query:-

SELECT b.name, a.createBy, a.updateBy
FROM news a
INNER JOIN admin b on a.id = b.id
WHERE a.id = $id;

Hope this can help you.

answered Jun 09 '14 at 06:17

Ankit Bajpai

13,128
4
25
40

The admin ID isn't the id of the news table. How will this work? – Fluffeh Jun 09 '14 at 06:22

score 0 · Answer 4 · answered Jun 09 '14 at 06:23

0

SELECT      admin_1.name AS CreateBY, admin.name AS UpdateBY
FROM         news INNER JOIN
                  admin AS admin_1 ON news.id = admin_1.Id CROSS JOIN
                  admin INNER JOIN
                  news ON admin.Id = news.Id

answered Jun 09 '14 at 06:23

Abdulrahman_88

545
1
5
15

2

C'mon guys. Look at the structure in the question and read what is written please. The id fields in the tables are not the common key. – Fluffeh Jun 09 '14 at 06:25
Actually it does you can try it yourself – Abdulrahman_88 Jun 09 '14 at 07:31

Query to get the data in related table

4 Answers4