CODEWITHSUNDEEP
sql-server
Rick
Rick

Reputation: 407

how to join tables sql server

Im having some trouble with joining two tables. This is what my two tables look like:

Table 1

Customer_ID   CustomerName   Add. 
1000          John Smith
1001          Mike Coles
1002          Sam Carter

Table 2

Sensor_ID     Location      Temp    CustIDFK
1000          NY            70
1002          NY            70
1000          ...           ...
1001
1001
1002

Desired:

Sensor_ID     Location      Temp    CustIDFK
1000          NY            70      John Smith
1002          NY            70      Sam Carter
1000          ...           ...     John Smith
1001                                Mike Coles
1001
1002

I have made Customer_ID from table 1 my primary key, created custIDFK in table 2 and set that as my foreign key. I am really new to sql server so I am still having trouble with the whole relationship piece of it. My goal is to match one customer_ID with one Sensor_ID. The problem is that the table 2 does not have "unique IDs" since they repeat so I cant set that to my foreign key. I know I will have to do either an inner join or outer join, I just dont know how to link the sensor id with customer one. I was thinking of giving my sensor_ID a unique ID but the data that is being inserted into table 2 is coming from another program. Any suggestions?

Upvotes: 1

Views: 6150

Answers (2)

ntziolis
ntziolis

Reputation: 10221

I'm not exactly sure where you actually see the problem, a simple natural join should do:

SELECT T1.CustomerName, T2.SensorID, T2.Location, T2.Temp
FROM Table1 AS T1, Table2 AS T2
WHERE T1.Customer_ID = T2.CustIDFK

Since you do not join on the IDcolumn of Table 2, but instead you join on the foreign key it does not matter that there is no unique key in Table 2.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

Based on your data, it looks like the value of Sensor_ID should instead exist in CustIDFK. I would then suggest making Sensor_ID an identity value within your sensor table, and make that value the primary key of this table.

If you want to associate the two tables, you should be able to simply join on Customer_ID and CustIDFK once it has been properly populated with the value currently in Sensor_ID.

SELECT
    t1.Customer_ID, t1.CustomerName, t2.Location, t2.Temp
From
    Table1 t1 JOIN
    Table2 t2 ON t1.Customer_ID = t2.CustIDFK

Upvotes: 4

Related Questions