Joining tables - SQL Server Forums

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

Joining tables

Author

Topic

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:04:09

Hi! I am trying to join two tables and use BETWEEN to restrict record selection. I have a table called, employee and a table called jobs. How do I write a query where I join the two and restrict the record selection by using the employee's salaries?

Thanks for any help!

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:07:48

Select *
From Table1

Inner Join Table2 on Table1.Column1 = Table2.Column1
Where "Whatever your trying to restrict" ie table1.date >= getdate()-90

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:09:39

I will try that, thanks!

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:10:53

depending on what information your going after try use the correct join, inner, left, right, full etc.

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:14:06

I was just joining the two tables and restricting the record selection by using salaries.

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:15:01

Now are you trying to join two tables or union?

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:18:28

I am writing an SQL query that joins two tables and uses BETWEEN to restrict record selection.(Use salary to restrict the data.)

Table 1 --> Employee
Table 2 --> Job

restrict by using salaries after joining

Thanks!

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:20:14

oh ok sorry about the confusion

that first statemetn should work, join the tables on columns that are similiar and then use the where statement to restrict the salary. Does that make sense?

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:24:43

Yes, I just join the two tables with a similar column and then restrict the records by using the where function, correct?

Thanks!

Scott

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:27:39

How does this look:

Select *
From Employee

Inner Join Job on Employee.Column1 = Job.Column1
Where Employee.salary >= getsalary()

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:29:42

kind of, getdate() is syntax which is todays date if you want to do something between a salary use
where Employee.salary between 20000 and 50000
you can also do
Where Employee.salary >= 30000

something like that.

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:32:25

Oh I see. What if I wanted to restrict the records by using LIKE after joining the tables?

You know by finding "LIKE" area codes of telephone numbers or a person's age?

Thanks!

Scott

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:42:51

what if it was a union to create a third table? How would that be done?

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:49:53

Well a union is a little different, union takes essential two tables and places them on top of each other for one table. joining you join on a similiar column where it becomes one table with the columns from each table together.

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:51:53

a like statement is for finding something similiar. say i wanted to find all the employee with a like name that start with sm i would use
where employee.lastname like ('s%')

Also you can join multiple tables together depending on what information your trying to get.

So if you have a

job table, employee table, and say a benefeits table you can join them together to get what info you need.

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:52:41

Oh, okay. Thanks for all of your help.
Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 16:55:49

sorry if i confused you there.

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 16:58:23

No, you were very helpful! I appreciate it so much! I have been playing around with this for a couple of days and have confused myself. I do hope to learn SQL.

One more question:

How would I group my employees? Using the same two tables, what if I wanted to group by salaries or classification?

Thanks again!

Scott

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-05 : 17:04:00

just use a group by

Select employee.name, job.salary
From Employee

Inner Join Job on Employee.Column1 = Job.Column1
Where Employee.salary between 20000 and 50000
Group By Job.salary, employee.name

Remember in the group by you have to use all the columns your pulling in from the tables so in this instance, employee and salary in the group by unless you using a funcion ie sum, min, max,avg get the the point.

cthegraves
Starting Member

13 Posts

Posted - 2007-12-05 : 21:32:05

What about if we have:

group employees by salary within their classification, select the employees last names and group them by salary within classification

I have a table named: employee and one named jobs, in these tables are records called last names, classifications and salaries

Then, another question is about exempt and non-exempt groups?

Thanks for any help!

Scott

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources