Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join's don't make sense to me

Author  Topic 

bAd_sLiNkY
Starting Member

2 Posts

Posted - 2013-07-19 : 18:11:13
Example: Say I created a "employee_table" that has 4 rows

ID firstName lastName
1 Robert Sims
2 Dwight Hummer
3 Mike Davis
4 Bob John


And a "department_table" that has say 10 rows, more than the employee table.

ID depart descrip
1 IT blahblah
2 Eng blahblah
3 Mkt blahblah
4 HR blahblah
5 Env blahblah
6 FIN blahblah
7 Acct blahblah
8 Budg blahblah

Each table will have its own Primary key and I understand I would need to assign a foregin key to the department table linking it to the main table employee_table, correct?

When I use inner join, I just don't understand how it knows which employee applies to which department. I have been using SQL tutorials on youtube and I also bought this book, "Head first into SQL by Lynn Beighley," but I just don't understand how these joins work or know how it knows rows matches to what. If by any means, I am not being clear please let me know so I can clarify. I'm assuming in this circumstance I am using an Inner join? Also if you guys can direct me to maybe a better tutorial platform please do.

For example employee bob john belongs to accounting department.

How would it know that ID 4 bob john should link too ID 7 which is Acct?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-19 : 18:27:17
Given your data, you wouldn't. You would need another column in the employee_table table that holds the foreign key value from the department_table table.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-19 : 18:28:04
It doesn't know how to link because you don't have a linking column. You need either a departmentID column in the employee table or a third table that contains employeeID and departmentID.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-19 : 18:29:07


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

bAd_sLiNkY
Starting Member

2 Posts

Posted - 2013-07-19 : 18:36:26
I think I'm catching on. I edited the table. I added a departID column to the original table, I also clarified the ID to empID in the original table as well. In that column I inserted a 7 for Bob John. Would this be the correct format? Sorry for the table not looking formated.

empID firstName lastName departID
1 Robert Sims
2 Dwight Hummer
3 Mike Davis
4 Bob John 7

departID department descrip
1 IT blahblah
2 Eng blahblah
3 Mkt blahblah
4 HR blahblah
5 Env blahblah
6 FIN blahblah
7 Acct blahblah
8 Budg blahblah


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-19 : 18:38:26
Yes looks good. Now you can join them via that departID column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-20 : 08:49:16
For my students, I normally teach them to design a third table, EmployeeDepartment, just in case one employee belongs to two or more departments.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -