|
View previous topic :: View next topic
|
| Author |
Message |
Chidane
New User
Joined: 25 Nov 2021
Posts: 22
Location: India
|
|
|
|
Hi Team,
My reqt is as follows:
I have joined Table 1 and Table 2 based on Cust ID and State code and got 1 row as follows with Columns as State Code - Cust ID - Cust Name - Eff Date.
| Code: |
| TN 123 CAR 2025-06-24 |
I have Table 3 that has State code, Cust ID, Eff Date that has multiple rows
| Code: |
TN 123 2020-10-01
TN 123 2025-06-25
TN 123 2025-10-15 |
I will have to check the results of Table1 and 2 with Table 3 (it can have matching rows (State code - Cust ID) or rows may not match too) and we need to have resultant rows as
| Code: |
TN 123 CAR 2025-06-24
TN 123 CAR 2020-10-01
TN 123 CAR 2025-06-25
TN 123 CAR 2025-10-15 |
Could you please help me with the logic to achieve this. I thought of joining Table 1 ,2,3 but Table 3 has only minimal rows and it may or may not match with rows of Table 1/2. That means all rows fetched from ytable 1 and table 2 along with the Effective date from Table 3. If multiple rows are there with Eff date, we need to have that much rows as resultant.
Thanks
Chidane |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
From your post it is not clear: what exactly prevents you from joining (not "checking"!) your initial table with Table 3?
What did you try so far?
Where is any code sample you tried?
What are the results you are not satisfied with?
Etc, etc, etc... |
|
| Back to top |
|
|
Chidane
New User
Joined: 25 Nov 2021
Posts: 22
Location: India
|
|
|
|
Hi Sergeyken,
The Table 1 and table 2 has huge number of rows.
While Table 3 has minimal number of rows and not all rows of Table 1/2 is present in Table 3.
However we need all rows of table 1 and 2.
So for every row fetched from Table 1 and 2, if there are multiple rows in Table 3, I need to write to flat file as Multiple records. |
|
| Back to top |
|
|
Chidane
New User
Joined: 25 Nov 2021
Posts: 22
Location: India
|
|
|
|
I have tried Joining all 3 tables, however only matching rows of Table 1 and 2 and 3 are only fetched.
In this case, I need all matching rows of table 1 and 2 and then for every multiple rows in Table 3, w eneed to create separate record entry and writre it to flat file. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
WHERE IS ANY SAMPLE OF THE USED CODE, AND EXACT COPY OF INPUT/OUTPUT DATA???
Without YOUR OWN samples of code you cannot expect others would give you a ready-to-copy-and-paste solution.
There is no need to start with "huge number of rows".
Run your initial tests with a limited table size: 2-3-5 rows in each of them, and present all your results here, - to receive any suggestion in response. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
Hint:
I hope it is clear that TWO STEPS are required?
1) Join Table 1 and Table 2, producing Table X?
2) Join Table X and Table 3, producing final Table/Report?
Each step must operate with TWO TABLES. There is no need to operate with THREE TABLES simultaneously... |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| Chidane wrote: |
| I have tried Joining all 3 tables, however only matching rows of Table 1 and 2 and 3 are only fetched. |
Looks like no more information and/or any tried code (hopefully, SQL query?) is expected here...
One more hint attempt.
In SQL, in order to join both matching and non-matching records from DB2 tables there are special constructions:
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Try to RTFM. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
Do table 1,2 inner join and table 3 left join .
In the where just say table3.state id is not null |
|
| Back to top |
|
|
|
|
