Can multiple joins be avoided for the same table?

Database
Enthusiast

Can multiple joins be avoided for the same table?

Hi All,

I have a requirement as below:

Table1:-

Group_ID - Member_ID

A - B

A - C

X - Y

X - Z

Table2:-

ID - Value1 - Value2

A - 111 - 222

B - 444 - 666

C - 566 - 556

X - 999 - 777

Y - 444 - 555

Z - 433 - 876

Output:-

Group_ID - Group_val1 - Group_val2 - Member_ID - Member_val1 - Member_val2

A - 111 - 222 - B - 444 - 666

A - 111 - 222 - C - 566 - 556

X - 999 - 777 - Y - 444 - 555

X - 999 - 777 - Z - 433 - 876

Logic:- I need to join Table1 to Table2 once with Group_ID to get values and again with Member_ID to get the values to get the required output.

Thought of checking whether the same can be achieved with a single join to get the required output.

Thanks a lot in advance.

Regards,

Sagar

6 REPLIES
N/A

Re: Can multiple joins be avoided for the same table?

Hello,

I think You want somethink like this:

SELECT
A.Group_ID,
B.Value1 as Group_val1,
B.Value2 as Group_val2,
A.member_ID,
C.Value1 as Member_val1,
C.Value2 as Member_val2
FROM Database.Table1 A
LEFT JOIN Database.Table2 B
ON A.Group_ID=B.ID
LEFT JOIN Database.Table2 C
ON A.member_ID=C.ID
ORDER BY 1,4

Regards
Peter
Enthusiast

Re: Can multiple joins be avoided for the same table?

Hi Peter,

Thank you and Yes exactly.

Just thought of checking whether this can be achieved with only one LEFT JOIN instead of 2 LEFT JOINs on the same table.

Regards,

Sagar

Junior Contributor

Re: Can multiple joins be avoided for the same table?

Hi Sagar,

you might do a LEFT JOIN using an ORed condition plus some CASE, but this will result in a real bad product join.

So better keep the two joins :-)

Dieter

Teradata Employee

Re: Can multiple joins be avoided for the same table?

Hello Dieter,

I need help in writing an SQL.

I have a Table "A", which has Emplyee ID, Joining Date, Resignation Date. [Resignation Date >= Joining Date].

I have another Table "B", which has the Days which are either Week-ends or, Business Holidays.

I wish to find how many days between Resignation Date & Joining Date is Week-ends/Business Holidays and Working Days for each Employee.

The way, I wrote the SQL:

Select A.Empid, A.Joining_Date, A.Resignation_Date, A.Resignation_Date - A.Joining_Date as "Total Days",

Sum(B.Calendar_Days) as "Non Working Days", "Total Days" - "Non Working Days" as "Working Days"

from A

Join B -- "B" is a Subquery where the Conditions Week-Ends & Business Holidays are Checked.

on B.Calendar_Days between A.Joining_Date + 1 and A.Resignation_Date;

The Problem with above SQL is the Product Join. The Volumne of Data in "A" is huge. The Product Join is rendering the Server very busy and forcing the DBAs to Abort.

Any help or suggestion in witing the above SQL in optimized way is appreciated.

Junior Contributor

Re: Can multiple joins be avoided for the same table?

That's a variation of the common problem "how many working days were needed to process this task":

Add a new SMALLINT column to your calendar table like holiday_seqnum and populate it once with a running number using

SUM(case when weekend then 1 else 0 end) over (order by calendardate rows unbounded preceding).

Now it's two joins to your calendar and a simple difference of the holiday_seqnums of Resignation_Date and Joining_Date.

Of course you might do the same for working days or simply do number of days between star end end minus number of holidays:-)

Dieter

Teradata Employee

Re: Can multiple joins be avoided for the same table?

Thanks a ton Dieter. I used your suggestion into the SQL and now,the running time is great. Your suggestion really made all the difference.