0

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

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Scott Davies
  • 103
  • 1
  • 13
  • Some DB servers have information_schema that may have a view or table containing relationships between tables already - [MySQL](http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular), [SQL Server](http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server), [Postgres](http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys), [Oracle](http://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference). – zedfoxus Jun 25 '15 at 13:03
  • Okay, any idea how to get this? I am using SQL Server 2008. – Scott Davies Jun 25 '15 at 13:13

0 Answers0