I have two tables as mentioned below.
user table
id | username | password | status |
1 | Prajna | ***** | active |
2 | Akshata | ***** | active |
3 | Sanjana | ***** | inactive |
test table
id | project_name | created_by (user id) | edited_by (user id) |
1 | Test | 1 | 2 |
2 | Trial | 1 | 1 |
3 | Pro1 | 2 | 2 |
I am trying with below query.
select project_name, user.username from test join user on user.id=test.created_by where user.status='active';
I wanted the result like below
I want to retrieve the result as below How can I retrieve?
project_name | username(created by) | username (edited by) |
Test | Prajna | Akshata |
Trial | Prajna | Prajna |
Pro1 | Akshata | Akshata |
Try this code.
create table `user` ( `id` int, `username` varchar(20), `password` varchar(20), `status` varchar(20) )
insert into `user` (`id`,`username`,`password`,`status`) values (1, 'Prajna', '*****', 'active'), (2, 'Akshata', '*****', 'active'), (3, 'Sanjana', '*****', 'inactive')
create table `test` ( `id` int, `project_name` varchar(20), `created_by` int, `edited_by` int )
insert into `test` (`id`,`project_name`,`created_by`,`edited_by`) values (1, 'Test', 1, 2), (2, 'Trial', 1, 1), (3, 'Pro1', 2, 2)
SELECT `t`.`project_name`, `ua`.`username` as 'username (created by)' , `ub`.`username` as 'username (edited by)' FROM `test` `t` JOIN `user` `ua` ON `t`.`created_by` = `ua`.`id` JOIN `user` `ub` ON `t`.`edited_by` = `ub`.`id` WHERE `ua`.`status` = 'active' AND `ub`.`status` = 'active' order by `t`.`id`
project_name | username (created by) | username (edited by) :----------- | :-------------------- | :------------------- Test | Prajna | Akshata Trial | Prajna | Prajna Pro1 | Akshata | Akshata
db<>fiddle here
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments