topic Re: join two tables without common key in Application Development and Automation Discussions

join two tables without common key

nielsvisser98 — Tue, 26 Jan 2021 11:17:59 GMT

Hi,

I have two different CDS views and I would like to join them. However, they have no common key on which I would like to join them. I would simply like to have the two tables in one CDS view, without changing any of the data or fields.

To visualuze my struggle: I have table A and B, and would like to get the code to come to the OUTCOME table:

Anyone who can help?

Thanks

Re: join two tables without common key

MateuszAdamus — Tue, 26 Jan 2021 11:33:56 GMT

Hello nielsvisser98

SELECT a1 AS a1, a2 AS a2, a3 AS a3, '' AS b1, '' AS b2, '' AS b3
  FROM table_A
UNION
SELECT '' AS a1, '' AS a2, '' AS a3, b1 AS b1, b2 AS b2, b3 AS b3
  FROM table_B

Kind regards,

Mateusz

Re: join two tables without common key

joltdx — Tue, 26 Jan 2021 11:37:00 GMT

Hi!

I believe you should be able to accomplish this with UNION. Here's the documentation.

The sets you UNION need to have the same structure, but I suppose it would be possible to "fake" that by selecting empty fields as B1, B2 and B3 for the table A select and vice versa.

Re: join two tables without common key

joltdx — Tue, 26 Jan 2021 11:38:39 GMT

Hah, mateuszadamus beat me to it. I'll leave my answer here with the link to the documentation... 🙂