I am writing a lot of SQL code at the moment - something I haven't done in a long time and getting frustrated at time spent working out relationships between tables.
I was thinking it might be a good idea to create in excel a list of the tables and how they join to other tables. Then you can simply use a drop down to select table1 and table2 and use a vlookup or similar to find how they link and generate the join.
I started but it occurred to me that someone may have already done this in an elegant way.
My process thus far has been to create a table in excel as per the table below. Then I can either have a dropdown or use a filter to find the appropriate relationship and get it to create the code for the required join:
Table1 |Table2 |Table1_Field |Table_ Field |Join Type cnsmr |cnsmr_accnt |cnsmr_id |cnsmr_id |inner cnsmr_accnt |UDEFGENERAL |cnsmr_accnt_id |cnsmr_accnt_id |inner cnsmr_Accnt_Tag |cnsmr_accnt |cnsmr_accnt_id |cnsmr_accnt_id |inner wrkgrp |cnsmr |wrkgrp_id |wrkgrp_id |inner
I can then use a formula like this (looking up on a list of tables to get the table abbreviation:
=" from " & A2 & " " & VLOOKUP(A2, 'List of Tables'!$A$2:$B$115, 2, FALSE)& " " & E2 & " join " & B2 & " " & VLOOKUP(B2, 'List of Tables'!$A$2:$B$115, 2, FALSE) & " on " &VLOOKUP(A2, 'List of Tables'!$A$2:$B$115, 2, FALSE) & "." &C2 & " = " & VLOOKUP(B2, 'List of Tables'!$A$2:$B$115, 2, FALSE) &"." &D2