cancel
Showing results for 
Search instead for 
Did you mean: 

Data Modeling

Naruto
Cosmonaut

I have table A and Table

Table A has below data    

inv_nameinv_id
1001A10000001
1002b10000001
AE-21F10000001
1004D10000002
UZ-WTS100000002

    TableB

pr_idsub_prd_id
10000001123456
1000000123456
1000000113456
10000001121212
10000002123456
1000000223456
1000000213456
10000002121212

 

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_nameinv_idpr_idsub_prd_id
AE-21F1000000110000001123456

 

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.

 

5 REPLIES 5

RADSr
Partner
Partner

Looking at your table definitions which columns are defined as keys?

Naruto
Cosmonaut

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?

 

RADSr
Partner
Partner

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.

suxinji
Employee
Employee

Hi @Naruto,

Here are two other solutions in Incorta: 

Optional 1: 

Using Incorta SQL Table. 

suxinji_1-1650660073638.gif

Optional 2: 

Business Schema - SQL view

This is a new feature in Incorta. 

suxinji_0-1650659850375.gif

Here are the results:

Screen Shot 2022-04-22 at 2.06.17 PM.png

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.