employee table have multiple employee name for same employee id. need to identify correct name from-CodePudding

Employee table have multiple employee name for same employee id. Need to identify correct name from name table and display matched row alone. In case name table don't have employee id present in employee table then display multiple employee name.
Emp table

id name   status
1  David   0
1  James   1
2  Kelvin  0
2  John    1

Name table
id    Name
1     James

Expected output

Id Name status
1 James  1
2 Kelvin 0
2 John   1

If I do inner join then I will get only match record. When emp id available in both employee and name then display only matched record, when empid present in employee table not available in name table then display all rows.

CodePudding user response：

Inner join the name table on the employee table

For example:

SELECT n.firstname, n.lastname FROM employee AS e INNER JOIN [name] AS n ON e.employee_id = n.employee_id

CodePudding user response：

One option is to union two sets: one for matching rows, and one for non-matching rows.

(Initially, there was the Oracle tag so I'm using sample data in a CTE form which works on Oracle; you wouldn't type that piece of code anyway so - disregard that):

Sample data:

SQL> with
  2  emp (id, name, status) as
  3    (select 1, 'David' , 0 from dual union all
  4     select 1, 'James' , 1 from dual union all
  5     select 2, 'Kelvin', 0 from dual union all
  6     select 2, 'John'  , 1 from dual
  7    ),
  8  name (id, name) as
  9    (select 1, 'James' from dual)
 10  --

Query begins here:

 11  -- matching rows (James)
 12  select e.id, e.name, e.status
 13  from emp e join name n on n.id = e.id and n.name = e.name
 14  union all
 15  -- non-matching rows (Kelvin and John)
 16  select e.id, e.name, e.status
 17  from emp e
 18  where not exists (select null
 19                    from name b
 20                    where b.id = e.id
 21                   )
 22  order by id;

        ID NAME       STATUS
---------- ------ ----------
         1 James           1
         2 Kelvin          0
         2 John            1

SQL>