New to writing in SQL - 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

New to writing in SQL

Author

Topic

ibdgrinch
Starting Member

4 Posts

Posted - 2008-06-21 : 16:12:50

I have some experience with MS SQL mostly installation, configuration, maintaining, etc. But I am trying to teach myself some TSQL. I have bought a few beginner books and have some test machines. In advance, I appreciate any feedback!

I have a database with two tables. I want to calc the min salary for a class of employees. The "wage" is in one table and the "status" is in another.

USE Database
GO
SELECT MIN(Wage) AS "Min Salary", Status
FROM Employee, Job
WHERE Status = '1'
GROUP BY Wage, Status
GO

The result set brings back all not the "status" class that I want.

Again, I am relatively new so take it easy on me!

Thanks,
grinch

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:16:58

You are doing a CROSS JOIN (cartesian product) since you select from two tables and you do not have a defined relation between the two tables.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:19:07

SELECT MIN({missing table alias}.Wage)
FROM Employee AS e
INNER JOIN Job AS j ON j.EmployeeID = e.EmployeeID
WHERE {missing table alias}..Status = '1'

You are also GROUPING by the Wage column, which you also want the minimum value from.

E 12°55'05.25"
N 56°04'39.16"

ibdgrinch
Starting Member

4 Posts

Posted - 2008-06-21 : 18:07:54

First of all thanks for responding. Do you mind if I walk through this statement.

Table1 is Employee, Table 2 is Job, Column 1 is Wage, Column 2 is Status

-- Select minimum value from Table1.Column1
-- From Table1 AS Table1 - e is representing the missing table alias?
-- Inner Join Table2 AS j ON Table2.Column2 = Table1.Column2 - j is representing the second missing table alias?
-- Where Table2.Status = '1'

I get the same min value for each class. What if there is a relation between the two tables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 03:16:08

quote:
Originally posted by ibdgrinch

First of all thanks for responding. Do you mind if I walk through this statement.

Table1 is Employee, Table 2 is Job, Column 1 is Wage, Column 2 is Status

-- Select minimum value from Table1.Column1
-- From Table1 AS Table1 - e is representing the missing table alias?
-- Inner Join Table2 AS j ON Table2.Column2 = Table1.Column2 - j is representing the second missing table alias?
-- Where Table2.Status = '1'

I get the same min value for each class. What if there is a relation between the two tables?

the join will represent the relationship b/w the tables. give the related field in ON part of inner join. Also group by field that represents your class and then take the min of wages field. If you need more help, please post table structures with some sample data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources