Home › Forums › MS SQL Server

Joins question

Mike I spent a few hours today going over joins and some other things. I
came across a question, if I want to add some tables to my project and there is no primary key or foreign key in those tables how do you link the table. I wanted to add address and state/province to my work and do some sorting. The sales address table only has a PK of addressed. It only show up in the customer address table.

‹ Running balance queries
Mon, 07/11/2011 - 18:11 — m_sabal

Inner joins

It's always nice to see students working on this outside of class :). This question takes a two-part answer:
1. If you are doing an inner join, which is most of the time, the use of foreign and primary keys is not necessary, though it does help speed things up tremendously. All you need is some means of connecting the two tables. So if the state is part of the purchase order number on a sales header but nowhere else, you can use a substring in the ON clause to form the link.
2. In some cases, it will take 3 or 4 joins to get the link you want. In this case, you link sales order to customer on customerID, customer to address on addressID, and address to state on stateCode. You don't have to select any columns from the intermediate tables; they're only there to provide the bridge to the information you really want.