How to model multiple many-to-one relationships using query builder?

[Drew]

We have a table that has 3 independent keys which point to a single second table; each of these keys is independent and represents a separate relationship. An example might be a single order which can relate to multiple salespeople.

Something like this:

Table 1

• Table 1 PK
• FK1 => Table 2 PK
• FK2 => Table 2 PK
• FK3 => Table 2 PK
• Column A
• Column B

Table 2

• Table 2 PK
• Column C
• Column D

I would expect to be able to connect the two tables, automatically adding multiple duplicate columns from the second table in my result set (perhaps with a prefix or suffix derived from the foreign key). So we might see something like this:

Joined table (expected)

• Table 1 PK
• Column A
• Column B
• FK1 Table 2 PK
• FK1 Table 2 Column C
• FK1 Table 2 Column D
• FK2 Table 2 PK
• FK2 Table 2 Column C
• FK2 Table 2 Column D
• FK3 Table 2 PK
• FK3 Table 2 Column C
• FK3 Table 2 Column D

When I try to include both tables in the query builder, the second table only appears once in the resulting columns. Additionally, no matter how I set up the relationships, the query builder attempts to group all 3 relationships together (join on FK1 = T2 PK AND FK2 = T2 PK AND FK3 = T2 PK). This is not reflective of the data model. We also only see one set of table 2's columns in the result, something like this:

Joined table (actual)

•  Table 1 PK
• Column A
• Column B
• Table 2 PK
• Table 2 Column C
• Table 2 Column D

Is there any way to do what I'm trying to do using the query builder? Or will I need to write manual SQL for this? Thanks!

[Silviya]

Hello,

I noticed that you've opened a support ticket on the same topic, so I'll post my answer here as well:

"The Query Builder will automatically detect any foreign key relationships between the added tables. However, you are right, if a certain table has more than one foreign key to certain other table, only the first foreign key would be displayed in query designer's Relationships Pane. 
I have tested with the AdventureWorks Database. The Sales.SalesOrderHeader table has two foreign key to Person.Address table (to BillToAddressID and ShipToAdressID fields), but there is only one relationship to this table - https://www.screencast.com/t/XoYAejsz4D (the first one).

Any additional relationships should be added manually (you will need to write them by hand inside the select statement). In this case you could use Manual mode to specify an join between ShipToAdressID  and AddressID fields - https://www.screencast.com/t/reMyFA8I.

Note that we consider the requirement as a valid feature request. If you wish, you could log this as new feature request in our feedback portal for voting from other users. The new features implementation in Telerik Reporting is prioritized based on several factors, one of which is user voting."

Regards,
Silviya
Progress Telerik