October 24, 2007 at 8:07 am
I'm having trouble using UNION. I have two tables:
CREATE Table JobTitle
(
JobID int NOT NULLPRIMARY KEY,
JobName varchar(50) NOT NULL,
JobDesc varchar(300)NOT NULL,
EEO1_Class varchar(50)NULL,
Exempt_NonExempt varchar(10) NOT NULL
);
CREATE Table Employee
(
EmpIDint NOT NULLPRIMARY KEY,
JobIDint NOT NULLREFERENCESJobTitle(JobID),
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
Address varchar(50) NOT NULL,
City varchar(25) NOT NULL,
State char(2) NOT NULL,
TelephoneAreaCode char(3) NOT NULL,
TelephoneNumber char(10) NOT NULL,
EEO1_Class varchar(30)NULL,
HireDate varchar(15)NOT NULL,
Salary moneyNOT NULL,
Gender char(2)NOT NULL,
Age intNOT NULL
);
-- Data Input Into JobTitle Table --
INSERT Into JobTitle
(
JobID,
JobName,
JobDesc,
EEO1_Class,
Exempt_NonExempt
)
VALUES
(
234,
'Cashier',
'Operates cash register to itemize and total customer’s purchases in grocery store.',
'Sales Workers',
'Non-Exempt'
);
INSERT Into JobTitle
(
JobID,
JobName,
JobDesc,
EEO1_Class,
Exempt_NonExempt
)
VALUES
(
188,
'Assistant Manager',
'Supervises and coordinates activities of workers in department of food store. Assists store manager in daily operations of store.',
'Officials & Managers',
'Exempt'
);
-- Data Input Into Employee Table --
INSERT Into Employee
(
EmpID,
JobID,
LastName,
FirstName,
Address,
City,
State,
TelephoneAreaCode,
TelephoneNumber,
EEO1_Class,
HireDate,
Salary,
Gender,
Age
)
VALUES
(
1,
234,
'Edelman',
'Glenn',
'175 Bishops Lane',
'La Jolla',
'CA',
'619',
'555-0199',
'Sales Workers',
'10/07/03',
$21500.00,
'M',
64
);
--MY PROBLEM IS HERE!! --
-- Calculates the max salary for exempt and non-exempt employees --
SELECT MAX(Salary) AS MaxSalaryExempt, MaxSalaryNonExempt
FROM Employee
INNER JOIN JobTitle
ON Employee.JobID = JobTitle.JobID
WHERE JobTitle.Exempt_NonExempt = 'Exempt'
UNION
SELECT MAX(Salary) AS MaxSalaryNonExempt
FROM Employee
INNER JOIN JobTitle
ON Employee.JobID = JobTitle.JobID;
WHERE JobTitle.Exempt_NonExempt = 'Non-Exempt';
GROUP BY Exempt_NonExempt
I keep getting an:
Msg 156, Level 15, State 1, Line number
Incorrect syntax near the keyword 'WHERE'
error
I guess my question is, am I using the UNION wrong?
I hope this question makes since, and I'm sorry post is so long.
Thanks for any help.
D.
October 24, 2007 at 8:19 am
You do seem to be using UNION incorrectly (it's not going to do what you want it to) .
That being said - you're getting the error because you have a stray semi colon in the middle of the second select within UNION statement.
If I understand what you want - it looks something like this:
select max(case when jt.Exempt_notExempt='exempt' then e.salary else 0 end) as exemptMax,
max(case when jt.Exempt_notExempt='exempt' then 0 else e.salary end) as exemptMax
from employee e inner join jobtitle jt on e.jobid=jt.jobid
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 24, 2007 at 8:55 am
Thank you.
October 24, 2007 at 9:25 am
You are using SQL Server 2005, right?SELECTExempt_NonExempt,
Salary
FROM(
SELECTjt.Exempt_NonExempt
Salary,
ROW_NUMBER() OVER (PARTITION BY jt.Exempt_NonExempt ORDER BY Salary DESC) AS RecID
FROMEmployee AS e
INNER JOINJobTitle AS jt ON jt.JobID = e.JobID
) AS d
WHERERecID = 1
N 56°04'39.16"
E 12°55'05.25"
October 24, 2007 at 9:38 am
Yes
October 24, 2007 at 9:43 am
Well... Did my suggestion work?
N 56°04'39.16"
E 12°55'05.25"
October 24, 2007 at 9:50 am
Thank you for your help. Got it working. Thank you again.
D
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply