CODEWITHSUNDEEP
sqloracle-database
nikhil carvalho
nikhil carvalho

Reputation: 41

How to get Oracle to return unique results in a one to many relationship tables with a left join

I have a three tables Table 1

Id  Department
1   A
2   B
3   C
4   D

Table 2

Id  DepartId    Name
1   1           ABC
2   1           DEF
3   1           ASD
4   2           FGH
5   2           HJK
6   3           ZXC

Table 3 

Id  Depart  Area
1   A       pp
2   B    
3   C       nn
4   D       oo

I need the result

Id  Depart  Name    Area
1   A       ABC     pp
2   B       FGH     Null
3   C       ZXC     nn
4   D       NULL    oo

I need one matching entry from table 2 and table 3 to corresponding entry in the table 1

Upvotes: 1

Views: 118

Answers (3)

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

by the way not the answer to your specific question but if instead of just one you want all the names you can use listagg

SELECT t1.id,
         department,
         LISTAGG (name, ',') WITHIN GROUP (ORDER BY name) names
    FROM t1, t2
   WHERE t1.id = t2.departId(+)
GROUP BY t1.id, department
ORDER BY 1

ID    Department         Names
1     A                  ABC,ASD,DEF
2     B                  FGH, HJK
3     C                  ZXC
4     D

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think I would do this with a correlated subquery:

select t1.*,
       (select t2.name
        from t2
        where t1.id = t2.DepartId and rownum = 1
       ) as t2name
from t1;

This saves the overhead of an aggregation. An index on t2(DepartId, name) is optimal for this query.

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Do a left join to also get t1 rows without any reference in the t2 table. GROUP BY to get only 1 row per Department.

select t1.id, t1.Department, min(t2.Name)
from t1
  left join t2 on t1.id = t2.DepartId
group by t1.id, t1.Department

Upvotes: 1

Related Questions