Problem with relationships

[Niels_T]

Hello,

I have 4 Tables that I have to use for my data.

The 4 tables are:

1. "Address"

2. "Order"

3. "Order Line"

4. "Product"

In two of those tables ("Order" and "Product") there are 2 "Brands" fields which include the brands of our company.

The problem is that when I use fields from the "Order" table and apply the "Brands" field from my "Product" table I won't get the desired result. If I use the "Brands" field from the "Order" table I do get the desired results, however, I don't have all the brands that I wanted to select as the "Brands" field in the "Product" table contains more brands, which allows me to analyse my data deeper.

The desired result is that I would like to use the "Product" Table together with my "Order" Table.

Their relationship is as followed:

The thing is here that I want to use the "Product" table together with both "Order Line" and "Order" table.

The "Product" table only works now with the "Order Line" table.

[lbendlin · Accepted solution]

Correct. Always, always follow the arrows. You cannot get from Products to Orders based on your current setup.  At a minimum you need to make the orders to order lines link bidirectional.

BEWARE:  This can have unintended consequences, especially in RLS scenarios.