Mysql – Substitute names for IDs stored in two columns referencing the same table

join;MySQL

I have 2 tables:

  • table1:

    id  no  name
1   501  john
2   354  sam
3   201  anderson

    and

  • table2:

    status  type      createdby  approvedby
1       recharge     2         1
1       topup        3         1

I need a query that would replace the createdby and approvedby references with corresponding names from table1, like this:

type           status           createdby         approvedby
recharge        1                 sam                john
topup           1                anderson            john

This is my attempted query:

SELECT `table2`.*, `tabl1`.`no`
FROM `table2`
JOIN `tabl1` ON `table2`.`createdby` =`table1`.`id`
JOIN `table1` `myTable` ON `table2`.`approvedby` =`orgTable`.`id`

But I am getting only createdby name, not approvedby name.

How can I solve this?

Best Answer

You were almost there.

First of all, your query contains what seems to be typos: there are two references to `tabl1` in it that should most likely be `table1` instead, and the `orgTable` alias should probably be `myTable` (or the other way round; my query below assumes the former).

Now with that out of the way, you can get the other name by referencing it via the `myTable` alias that you assign to the second instance of table1:

SELECT `table2`.`type`,
       `table2`.`status`,
       `table1`.`name`  AS createdBy,
       `myTable`.`name` AS approvedBy
FROM `table2`
JOIN `table1` ON `table2`.`createdby` = `table1`.`id`
JOIN `table1` AS `myTable` ON `table2`.`approvedby` = `myTable`.`id`;

Let me also note that your query will arguably be clearer if you use aliases for both instances of table1, as well as avoid excessive use of backticks:

SELECT table2.type,
       table2.status,
       creator.name  AS createdBy,
       approver.name AS approvedBy
FROM table2
JOIN table1 AS creator  ON table2.createdby  = creator.id
JOIN table1 AS approver ON table2.approvedby = approver.id;
Related Question