SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join's don't make sense to me
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bAd_sLiNkY
Starting Member

2 Posts

Posted - 07/19/2013 :  18:11:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/19/2013 :  18:27:17  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 07/19/2013 :  18:28:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
36845 Posts

Posted - 07/19/2013 :  18:29:07  Show Profile  Visit tkizer's Homepage  Reply with Quote


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 - 07/19/2013 :  18:36:26  Show Profile  Reply with Quote
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



Edited by - bAd_sLiNkY on 07/19/2013 18:40:14
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 07/19/2013 :  18:38:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Sweden
30213 Posts

Posted - 07/20/2013 :  08:49:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000