0

In below teradata SQL code I want to join 3 different columns from table A (col1,col2,col3) with employee table's name column. Below operation working fine but want to perform this operation within one join, can we do that with alternate way to avoid multiple joins on same table? I'm getting spool space error due multiple joins with views.

Select (case when P.name is not null then A.col1 else null end)name1,
       (case when Q.name is not null then A.col2 else null end)name2,
       (case when R.name is not null then A.col3 else null end)name3
From A Left join
     employee P
     on P.name = A.col1 Left join
     employee Q
     on Q.name = A.col2 Left join
     employee R on R.name = A.col3
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Ankush Gondane
  • 11
  • 1
    Please provide sample data, desired results, and an explanation of what the code should be doing. – Gordon Linoff Sep 09 '20 at 11:04
  • First you should not join on employee.name but rather on employee.id which should be a primary key of the employee table and a foreign key between employee and A – Kirjava Sep 09 '20 at 11:20
  • While it would be valid SQL to say `From A Left join employee X on X.name = A.col1 OR X.name=A.col2 OR X.name=A.col3`, there is no guarantee that will perform better. – Fred Sep 09 '20 at 17:19

0 Answers0