General question on SQL - JOINS

Question

If I was asked to query JOIN of more than three tables, what is the best way I go about understanding the relationship of the tables before I code. Should I use Database Diagram in SQL Server, or would I be given the necessary information? What would you recommend?

Thanks in advance for your time.

Answer 1

You could use the diagramming tools in SQL Server Management Studio to discover any Foreign Key relationships between those tables. It might be quicker than using the GUI to inspect each table in Design mode, and viewing its Relationships dialog.

Consider creating an ad-hoc View with those 3 tables. This will help you produce the SQL statement that you'd need. If any relationships exist on those 3 tables, you'll have the JOIN statement created for you by the tool.

• Right click Views -> New View.
• Pick the tables you need, click Add
• all relationships are displayed in the Diagram Pane, and the SQL Pane will the SELECT statement with the required JOINS.

Answer 2

Depending on the convention they use for creating foreign keys, it shouldn't be to hard to find the relationships between tables.

The convention we use is

dbo.TableA(ID PK)
dbo.TableB(ID PK, TableAID FK)
dbo.TableC(ID PK, TableBID FK)
...

If they don't use any convention at all or didn't even create Foreign Key constraints, you can take that as an opportunity to educate them about the importance of conventions aka the lost time and money by not using them.