Joins and Relationships Theory the Pearls and the

Joins and Relationships Theory, the Pearls and the Perils IDEA User Group 8 th June 2017

Relationships Nothing is perfect. Life is messy. Relationships are complex. Outcomes are uncertain. People are irrational. Hugh Mackay

Relationships Many to one relationship Foreign key Key

Some Diagrammatic Joins Inner Join Left Excluding Join (Matches Only) (All records in primary file) (Records with no secondary match) Right Excluding Join Outer Excluding Join (Records with no primary match) (All records in both files) (Not available in IDEA)

Example Tables

Inner Join (Matches Only) Set db = Client. Open. Database("Table 1. IMD") Set task = db. Join. Databasetask. File. To. Join "Table 2. IMD"task. Include. All. PFieldstask. Include. All. SFieldstask. Add Match Key "PRIMARYKEY", "A"task. Create. Virtual. Database = Falsedb. Name = ". IMD" task. Perform. Task db. Name, "", WI_JOIN_MATCH_ONLY Set task = Nothing Set db = Nothing Client. Open. Database (db. Name)

Inner Join Result

Left Join (All records in primary file) Set db = Client. Open. Database("Table 1. IMD") Set task = db. Join. Databasetask. File. To. Join "Table 2. IMD"task. Include. All. PFieldstask. Add. SField. To. Inc "VALUE"task. Add. Match. Key "PRIMARYKEY", "A“ task. Create. Virtual. Database = False db. Name = ". IMD"task. Perform. Task db. Name, "", WI_JOIN_ALL_IN_PRIM Set task = Nothing Set db = Nothing Client. Open. Database (db. Name)

Left Join Result

Left Excluding Join (Records with no secondary match) Set db = Client. Open. Database("Table 1. IMD") Set task = db. Join Databasetask. File. To. Join "Table 2. IMD"task. Include. All. PFieldstask. Add. Match. Key "PRIMARYKEY", "A“ task. Create. Virtual. Database = Falsedb. Name = ". IMD" task. Perform. Task db. Name, "", WI_JOIN_NOC_SEC_MATCH Set task = Nothing Set db = Nothing Client. Open. Database (db. Name)

Left Excluding Join Result

Right Excluding Join Left Excluding Join (Records with no primary match) Set db = Client. Open. Database("Table 1. IMD") Set task = db. Join. Databasetask. File. To. Join "Table 2. IMD"task. Include. All. PFieldstask. Include. All. SFieldst ask. Add. Match. Key "PRIMARYKEY", "A"task. Create. Virtual. Database = Falsedb Name = ". IMD" task. Perform. Task db. Name, "", WI_JOIN_NOC_PRI_MATCH Set task = Nothing Set db = Nothing Client. Open. Database (db. Name)

Right Excluding Join Result

Outer Join (All records in both files) Set db = Client. Open. Database("Table 1. IMD“ )Set task = db. Join. Databasetask. File. To. Join "Table 2. IMD"task. Include. All. PFieldstask. Include. All. SFieldstask. A dd. Match. Key "PRIMARYKEY", "A"task. Create. Virtual. Database = Falsedb. Name = ". IMD" task. Perform. Task db. Name, "", WI_JOIN_ALL_REC Set task = Nothing Set db = Nothing Client. Open. Database (db. Name)

Right Excluding Join Result

Visual Connector

Other Joins Outer Excluding Join (Why? ) If you really need to, it’s a left excluding join and a right exluding join, then append the two databases. Watch out for the Cartesian Join A sales ledger with 50, 000 records relating to 150 account codes will result in an output of 7. 5 million records (and the extract could take some time to run. )

Cartesian Join IDEA won’t allow you to do this, but it’s very easy to do accidentally in Access. You just need to forget to create a relationship between two tables.

A Recommendation for Polling Day Party Vote Optimism Defeatism IDEAlism Antidisestablishmentarianism Humanism Pragamatism Schism X

Thank you! Any questions? Dave Pendreigh dave. pendreigh@hmrc. gsi. gov. uk