Join where second table has dupes!

Database
Enthusiast

Join where second table has dupes!

Now here are my 2 tables :
STORY_OUT
CUST_ID CUST_TYP MSG_ID MSG_TX
123 EVT 234 THIS EVT IS CASE FOR

This will find 2 matches in CSA table as below:
CSA
CUST_ID CUST_TYP FIELD_NM FIELD_TX
123 EVT PARTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT REASON_CD CASH DEPOSIT

How can I join these 2 tables and get both records in the second table?
Basically what I need to do is replace party name and the Reason cd in MSG_TX with the values in FIELD_TX, but I am just trying to create a temp table as below:

STORY_OUT_TMP
CUST_ID CUST_TYP MSG_ID MSG_TX FIELD_NM FIELD_TX
123 EVT 234 THIS EVT IS CASE FOR PARTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY

Any help is greatly appreciated!
5 REPLIES
Enthusiast

Re: Join where second table has dupes!

Try this:

INSERT INTO STORY_OUT_TMP
SELECT
FROM STORY_OUT O
INNER JOIN
CSA C
ON C.Cust_ID = O.Cust_Id
AND C.Cust_Typ = O.Cust_Typ
AND C.Field_Nm = 'PARTY_NAME';
Enthusiast

Re: Join where second table has dupes!

I cannot specify the Field_NM because the field_nm willl be dofferent for each recoord like party name, reason, transaction amount etc.
Enthusiast

Re: Join where second table has dupes!

Hi

you can use cross join here.

SELECT
FROM STORY_OUT O
CROSS JOIN
CSA C
ON C.Cust_ID = O.Cust_Id
AND C.Cust_Typ = O.Cust_Typ;
Enthusiast

Re: Join where second table has dupes!

Tried but...cross join does not give me the expected result. :(
Senior Apprentice

Re: Join where second table has dupes!

"How can I join these 2 tables and get both records in the second table?"

Simply joining them?

What do you actually want?

Join STORY_OUT to only one of the rows in CSA?
This fits to your STORY_OUT_TMP row.
But this leads to the question: Which row?

Or return both rows in CSA combined into a single row?

untested:
SELECT *
FROM STORY_OUT O
INNER JOIN
(select CUST_ID, CUST_TYP, FIELD_NM, FIELD_TX,
min(FIELD_NM) over (partition by Cust_Id, C.Cust_Typ order by FIELD_NM
rows between 1 following and 1 following) ,
min(FIELD_TX) over (partition by Cust_Id, C.Cust_Typ order by FIELD_NM
rows between 1 following and 1 following)
from CSA
qualify row_number() over (partition by Cust_Id, C.Cust_Typ order by FIELD_NM) = 1
) C
ON C.Cust_ID = O.Cust_Id
AND C.Cust_Typ = O.Cust_Typ
;

What if there are more than two rows in CSA?

Dieter