we would like to join 2 data extensions in Marketing Cloud based on the 1-to-many relationship. To make it simple, among other attributes, the first DE contains the FederationID, as PrimaryKey, and fields such as FirstName, LastName, Country, etc The second DE contains FederationID (ForeignKey) , EmailAddress, Optin status, etc. The csv file that we receive has cases where there are multiple email addresse records associated to only one FederationID. Is there a way to join via SQL the 2 data extensions in a way that in the target DE we have the FederationID having multiple email addreses assigned to it when needed? Is there another way beyond UNION ALl and removing the Primary Key in the target DE?
Your target DE can't have PK on FederationID, it would need to be a composite key of FederationID and Emailaddress or no PK at all. I don't see why you would need to use a UNION ALL