Many to Many Relationship - 3+ Tables

Scenario:
(will be using terms from Criminal Minds)

Table: Intake

Intake collects up to

• 5 Phone Numbers
• 5 Email Addresses
• 5 Websites
• 1 Name
• 1 Alias

Names are not Unique
Alias Must be

Table: Info
Each Verified Phone Number has a new record with the Phone and Alias
Each Verified Email has a new record with Email and Alias
Each Verified Website has a new record with Website and Alias


Table: Alias

Contains All known Aliases
Uses a Report Link to pull

• Every Phone associated to the Alias from Info
• Every Email Associated to the Alias from Info
• Every Website Associated to the Alias from Info
• Every Name associated to the Alias from Info

Table: Unsub
Links multiple Alias's together based on what our goal state is.



Goal:
We want to from the Alias table see any other Alias that a given Phone,Email, or Website may be linked to.


Any given piece of the contact info could show up on more than one Alias


Current State:
Any attempts I've made to run reports or build relationships from the Alias table:

•  only return data that matches the Alias
• Does not return related data to the given phone, email, or website regardless of how I've built the Report Links. 

Example:
Intake record could have:

Record 1:
Alias:  BAU
Name: JJ
Phone 1: 202 555 5555
Phone 2: 555 867 5309

Record 2
Alias: BAU 1
Name: Reed
Phone 1: 555 123 4567
Phone 2: 202 555 5555



When I look at the Alias table for BAU, I want to see that 202-555-5555 also showed up in BAU1