one to many relationship in joins

[snt_ds]

Hi
I have table ABC and need to join on DEF.
relation ship on ABC and DEF is one to many.

if my key column for join is COLUMN_TEST.
suppose COLUMN_TEST in ABC table has a value 123
and same COLUMN_TEST in DEF may have multiple records with value 123.

Can some please let me know how to acomplish this task.

ABC has 10 records after my join with DEF it have 100 or 1000....records.

Thanks
Suri

[ray.wurlod]

What you seek to do is a common or garden variety inner join, on the join key column whose value is 123 in your example. Where are you experiencing difficulty?

[snt_ds]

Thanks Ray for your quick responce.

I doing the left outter join. I have 1 to many relationships from left to right. How can I accomplish this in the job ( Do I need to Pivot the records).

This job is for delta loads
In my left table I will get nearly 60,000 records every day.
If I pivot this then I will have more then million records.

I trying to come up with some logic here.



Thanks
Suri

[ray.wurlod]

No. All you need is an inner join. Nothing else is required. Inner join will give the correct number of rows.

Left outer join will give additional rows with only NULLs from the right input. This is not what you require, according to the specification you have posted here.

[suri]

I can't do the inner join as I need to get all records from the left table.
There will be multiple records in the right table for one perticular key value.

Thanks
Suresh

[sachin1]

hello, i am not aware of parallel Extender, but hope their will be something similar for server edition, stream records and reference..

so you use your child table in stream records and parent table in reference.

[ray.wurlod]

Then use a left outer join.

[Minhajuddin]

Why don't you understand what Ray says

The INNER JOIN is the join for you.
Let me explain.

say your source is

ABC
===
Col1 Colx
======
1 Me
2 You

DEF
===
Col1 Col2
======
1 A
1 B
1 C
2 D
2 E
2 F


And if you have a simple INNER JOIN on Col1. then the o/p will be:
and have Col1,Colx from the left i/p link and Col2 from the right i/p link mapped to the o/p
This will be your result

Col1 Colx Col2
==========
1 Me A
1 Me B
1 Me C
2 You D
2 You E
2 You F



It can't be clearer than this.

[Minhajuddin]

You would want to use a Left Outer Join only when you want the records which don't have a match in the right link to be sent to the output.