link table with just one common field for many-to-many relationship between tables

[joioan]

Hello experts!

I've got this case where I have two tables with many-to-many relationships between them

the tables are like:

Family_Members:

Family_ID,

Family_Member_ID,

otherfields

from Table1;

Tickets:

Family_Member_ID,

Family_ID,

Ticket_ID,

IssueDate

from Table2;

Here in my case the common field is a concatenation of Family_ID&' '&Family_Member_ID. Would this work?

[Taoufiq_Zarra]

@joioan 

Family_Members:
LOAD
    Family_ID,
    Family_Member_ID,
    Family_ID & ' ' & Family_Member_ID as FamilyMemberKey,
    otherfields
FROM Table1;

Tickets:
LOAD
    Family_Member_ID,
    Family_ID,
    Family_ID & ' ' & Family_Member_ID as FamilyMemberKey,
    Ticket_ID,
    IssueDate
FROM Table2;

[joioan]

And what if two fact tables have again many to many relationship but a single common field?

Let's examine this scenario:

Family_Members:

Family_ID,

Family_Member_ID,

otherfields

from Table1;

Logins:

Login_ID,

Family_Member_ID,

Login_Date

From table 3;

@Taoufiq_Zarra (thank you!)

[Taoufiq_Zarra]

Since Family_Member_ID is common in both tables, you can link the tables directly through this field.

SELECT
fm.Family_ID,
fm.Family_Member_ID,
l.Login_ID,
l.Login_Date,
fm.otherfields
FROM Family_Members fm
LEFT JOIN Logins l
ON fm.Family_Member_ID = l.Family_Member_ID;

[joioan]

@Taoufiq_Zarra , my bad the case with logins would be

Family_Members:

Family_ID,

Family_Member_ID,

otherfields

from Table1;

Logins:

Login_ID,

Family_ID,

Login_Date

From table 3;

in both tables family_id appears many times and it seems to be the only common field there is.

[joioan]

Any help would be so helpful!

[p_verkooijen]

Hi @joioan if there is only one common field between the tables there are no issues. 

Or is the issue that the data does not correspond, values could be different between the datasets.

What are you experiencing, do you have an example of what goes wrong?

[joioan]

Hello @p_verkooijen . I have learnt that since a many-to-many relationship is present I have to handle it with link tables or concatenated tables, but in this case a link table is suitable. How will I work with that?

[p_verkooijen]

@joioan not sure why a many to many relationship is affecting your expected output.

Hard to say without some example data. 
Create a simple example set. What do you have and what would be the expected output.