SQL Query by using left or right outer join

Question

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?

What SQL have you tried? This reads like a homework problem and SO isn't a place where people do you homework for you. — saritonin, Feb 20 '19 at 19:24
I've tried this query: "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 — user10251274, Feb 20 '19 at 19:29
Try adding more where clauses -- e.status = 'pending' and e2.status = 'failed' — saritonin, Feb 20 '19 at 19:41

score 0 · Accepted Answer · answered Feb 20 '19 at 19:41

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

View on DB Fiddle

You actually understood the question? Do you think you could edit it to make it clearer? — Barmar, Feb 20 '19 at 19:55
Thank you @Simonare its working. Can it be possible to achieve without using union all ? — user10251274, Feb 20 '19 at 19:59

SQL Query by using left or right outer join

1 Answers1