04-20-2022 08:30 AM
I have table A and Table
Table A has below data
inv_name | inv_id |
1001A | 10000001 |
1002b | 10000001 |
AE-21F | 10000001 |
1004D | 10000002 |
UZ-WTS | 100000002 |
TableB
pr_id | sub_prd_id |
10000001 | 123456 |
10000001 | 23456 |
10000001 | 13456 |
10000001 | 121212 |
10000002 | 123456 |
10000002 | 23456 |
10000002 | 13456 |
10000002 | 121212 |
when i join table A(Child) with Table B(Parent) on inv_id = pr_id i'm getting i'm getting single parent record
inv_name | inv_id | pr_id | sub_prd_id |
AE-21F | 10000001 | 10000001 | 123456 |
how can i get all the sub_prd_id id details mapped each prd_name in incorta?
i'm getting it with materialised view , is there any other solution to achive it.
04-20-2022 09:06 AM
Looking at your table definitions which columns are defined as keys?
04-22-2022 08:58 AM
i don't have any key columns in these table.
what i'm trying to achive is if there are multiple records for a single product_id , if i join the table with inv_id(have same value as product_id) , for a single inv_id has multiple items , how could we achive it with out bridge table?
04-22-2022 10:05 AM
Ah - sorry - my first look at the table was too quick!
I don't believe you can force the Incorta query engine to do that between the two tables.
As you pointed out you'll need an MV ( or maybe a derived table - I don't have access to an environment to do a quick test at the moment ).
You could write a SQL statement into a single Incorta table using a SQL Database source, but I'm not sure you'd see much advantage to doing that unless you truly are never going to need tables A and B.
04-22-2022 02:08 PM - edited 04-23-2022 02:32 PM
Hi @Naruto,
Here are two other solutions in Incorta:
Optional 1:
Using Incorta SQL Table.
Optional 2:
Business Schema - SQL view
This is a new feature in Incorta.
Here are the results:
For the above solutions, I'm using Incorta Cloud Release: 2022.4.0.
Please let me know if these two solutions work for you.
Feel free to let me know If you have any other questions.