I've table name as "Employee" with respective columns(id,employee_id and status). If status is in "pending" and take pending status of "employee_id" and lookup into other records of employee_id column. So employee_id is exist then I've to take id value and store into result_id.
id employee_id status
1 1000 failed
2 1001 failed
3 1002 failed
4 1005 failed
5 1006 failed
6 1005 pending
7 1004 pending
8 1001 pending
9 1002 pending
10 1006 pending
Example : id=6,employee_id=1005 and status='pending' then result_id should be 4 (i.e result_id=4)
Output :
id result_id
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 4
7 NULL
8 2
9 3
10 5
I've tried:
select e.id as id ,e2.id as result_id
from employee as e, employee as e2
where e.employee_id=e2.employee_id and e.id not in (e2.id)
This query will return only value of result_id but I want Null values too.
Can anybody help me on this?
Check this:
Schema (MySQL v5.7)
CREATE TABLE t (
`id` INTEGER,
`employee_id` INTEGER,
`status` VARCHAR(12)
);
INSERT INTO t
(`id`, `employee_id`, `status`)
VALUES
('1', '1000', 'failed'),
('2', '1001', 'failed'),
('3', '1002', 'failed'),
('4', '1005', 'failed'),
('5', '1006', 'failed'),
('6', '1005', 'pending<br/>'),
('7', '1004', 'pending<br/>'),
('8', '1001', 'pending<br/>'),
('9', '1002', 'pending<br/>'),
('10', '1006', 'pending<br/>');
Query #1
select id, null x from t where status = 'failed'
union all
select t.id, t2.id from t
left join t t2
on t.employee_id = t2.employee_id and t2.status = 'failed'
where t.status != 'failed';
| id | x |
| --- | --- |
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 8 | 2 |
| 9 | 3 |
| 6 | 4 |
| 10 | 5 |
| 7 | |