CODEWITHSUNDEEP
sqloracle-database
Abinash Pattnaik
Abinash Pattnaik

Reputation: 51

How to get result if exactly one match inner join

How can I write a query to join two tables and return result if exactly one match in there. I have to discard results if zero match and more than one match.

All I am looking for is to extend the INNER JOIN. Let me just get to the point. I have two tables Dept & Emp. One Dept can have multiple Emp's & not the other way around.

     Table Dept

enter image description here

      Table Emp

enter image description here

I need to JOIN it on Dept_id

   Expected Results

enter image description here

Upvotes: 3

Views: 1649

Answers (6)

priyanka pardeshi
priyanka pardeshi

Reputation: 51

Also, try below query;

SELECT a.depid dept_id,dept_name,emp_id,emp_name
FROM
(SELECT case WHEN count(*)=1 THEN dept_id END depid FROM emp GROUP BY dept_id) a INNER JOIN emp ON depid=dept_id
INNER JOIN dept b ON a.depid = b.dept_id 
WHERE depid IS NOT NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can phrase this as an aggregation query in Oracle:

select d.dept_id, d.dept_name,
       max(e.emp_id) as emp_id, 
       max(e.emp_name) as emp_name
from dept d inner join
     emp e
     using (dept_id)
group by d.dept_id, d.dept_name
having count(*) = 1;

This works because if there is only one match, then max() returns the value from the one row.

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

Another way would be

select d.dept_id, d.dept_name, e.emp_name
from   emp e
       join dept d on d.dept_id = e.dept_id
where  e.dept_id in
       ( select dept_id from emp group by dept_id having count(*) = 1 )

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a subquery for group by dept_id haing count = 1 

 select  t.dept_id, dept.dept_name, emp.Emp_name
 from (
  select dept_id
  from emp 
  group by  dept_id 
  having count(*) = 1
 ) t 
 INNER JOIN dept on t.dept_id = dept.dept_id 
 INNER JOIN emp ON t.dept_id = emp.dept_id

Upvotes: 1

igr
igr

Reputation: 3499

One alternative to existing solutions can be one using analytics (window functions), instead of joining twice: 

select dept_id, dept_name, emp_id, emp_name
from
(    
  SELECT 
    d.Dept_id, d.Dept_name, e.Emp_id, e.Emp_Name, 
    count(*) over (partition by d.dept_id) cnt1
  FROM d 
  INNER JOIN e
  ON d.Dept_id = e.Dept_id
) where cnt = 1;

Upvotes: 3

GMB
GMB

Reputation: 222482

You can join with a not exists condition:

select d.*, e.emp_id, e.emp_name
from dept d
inner join emp e
    on d.dept_id = e.dept_id
    and not exists (
        select 1
        from emp e1 
        where e1.dept_id = d.dept_id and e1.emp_id != e.emp_id
    )

Upvotes: 2

Related Questions