Basic Select Problem - 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

Basic Select Problem

Author

Topic

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 09:11:39

Basically, im being asked Which is the maximal budget of projects in which employee 'John Smith' is involved?

I got a nested query done kinda right, like this

Select max(Budget)
From aProject
Where (aProject.Pid = (
Select Pid
From aWorks_For
Where aWorks_For.Eid = (
Select Eid
From aEmployee
Where Ename = 'John Smith')))

But when an employee is working on more then one project I get this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Heres my Create Table Statements

Any help would be appreciated

CREATE TABLE EMPLOYEE ( EId INT, EName VARCHAR(20), Salary INT, PRIMARY KEY (EId) )

CREATE TABLE WORKS_FOR ( EId INT, PId INT, PRIMARY KEY (PId, EId) )

CREATE TABLE PROJECT ( PId INT, PName VARCHAR(20), Supervisor VARCHAR(20), Budget INT, PRIMARY KEY (PId) )

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-01 : 09:20:35

Try this query,

SELECT MAX(A.Budget)
FROM Project A INNER JOIN Works_For B ON A.PId = B.PId
INNER JOIN Employee C ON B.EId = C.EId AND C.EName = 'John Smith'

SQL Server Helper
http://www.sql-server-helper.com

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 09:21:42

Anyway of doing it without using joins?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-01 : 09:37:33

If you don't want to use joins, change the = to IN in your query:

Select max(Budget)
From aProject
Where (aProject.Pid IN (
Select Pid
From aWorks_For
Where aWorks_For.Eid IN (
Select Eid
From aEmployee
Where Ename = 'John Smith')))

SQL Server Helper
http://www.sql-server-helper.com

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 09:39:24

Thats perfect, thanks buddy.

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 10:12:57

Im having another problem aswell.

I want to select the PName and Pid aswell as the sum of all the salaries of the employees working on it. Any ideas how I would go about doing this in a Nested Query? Im not sure how to start it.

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 10:39:52

Anyone?

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 11:21:46

Hmm, could it be done using a Create View thing?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-01 : 11:43:14

Anarking -- if you don't feel comfortable with JOINS, and you are trying to write SQL code, then you are in BIG trouble. I strongly recommend practicing them and getting used to them. They are absolutely fundamental when it comes to writing clear, efficient SQL code -- the entire premise of a relational database relies on them! You can't keep trying to "avoid" them by creating huge nested WHERE IN ( WHERE IN (... etc )) clauses, that is really bad code.

Joins are easy to use if you practice, and also I recommend reading a good book on writing SQL. the basics are so important and will make your life much, much easier in the long run.

- Jeff
http://weblogs.sqlteam.com/JeffS

Anarking
Starting Member

13 Posts

Posted - 2007-05-01 : 11:54:36

But isnt the other thing just a Create View statement?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-01 : 12:05:04

What other thing?

- Jeff
http://weblogs.sqlteam.com/JeffS

Anarking
Starting Member

13 Posts

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

I need to Print off The Project Name, The Project Id and the Sum of all the employees working on the project.

Im not sure how to do it but I imagine its something like

Create View Project Thing
Then With PID And PName
With some Statement for adding up the salaries fo all employees involved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources