How can i Join these 2 table ? I am getting an error my LEFT OUTER JOIN
DOESNT WORK thanks
SELECT EmployeeNo, Datehired, SectionCode,
Department,Lastname,Firstname
FROM (dbo.Employees H1
GROUP By EmployeeNo, Datehired, SectionCode,
Department,Lastname,Firstname)
dbo.Employees LEFT OUTER JOIN ON dbo.empBenefits.EmployeeNo =
dbo.Employees.EmployeeNo
SELECT EmployeeNo, StartDate,Amount AS OLDBENEFITS,
EndDate,BenefitCode,
(SELECT MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND
startdate >= '2000-01-01' AND startdate <= '2007-03-30')
AS NEWBENEFITS
FROM dbo.empBenefits T
WHERE(StartDate =(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND
Benefitcode = 'HON' AND startdate >= '2000-01-01' AND startdate <=
'2007-03-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amountheri wrote:
> How can i Join these 2 table ? I am getting an error my LEFT OUTER JOIN
> DOESNT WORK thanks
> SELECT EmployeeNo, Datehired, SectionCode,
> Department,Lastname,Firstname
> FROM (dbo.Employees H1
> GROUP By EmployeeNo, Datehired, SectionCode,
> Department,Lastname,Firstname)
> dbo.Employees LEFT OUTER JOIN ON dbo.empBenefits.EmployeeNo =
> dbo.Employees.EmployeeNo
> SELECT EmployeeNo, StartDate,Amount AS OLDBENEFITS,
> EndDate,BenefitCode,
> (SELECT MAX([amount])
> FROM EMPBENEFITS T2
> WHERE T2.employeeno = T.employeeno AND
> startdate >= '2000-01-01' AND startdate <= '2007-03-30')
> AS NEWBENEFITS
> FROM dbo.empBenefits T
> WHERE(StartDate =(SELECT TOP 1 ([startdate])
> FROM EMPBENEFITS T1
> WHERE T1.employeeno = T.employeeno AND
> Benefitcode = 'HON' AND startdate >= '2000-01-01' AND startdate <=
> '2007-03-30'))
> GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
It seems like you have two queries here. I can't quess how you intended
to combine them. It would help if you post CREATE TABLE statements for
the tables, some sample data and explain what result you want. Always
tell us the content of any error messages and always say what version
of SQL Server you are using. "Getting an error" is not a helpful
description of your problem!
SELECT E.employeeno, datehired, sectioncode, department, lastname,
firstname
FROM dbo.Employees AS E
LEFT JOIN dbo.empBenefits AS B
ON B.employeeno = E.employeeno ;
SELECT employeeno, startdate, amount AS oldbenefits, enddate,
benefitcode,
(SELECT MAX([amount])
FROM empbenefits AS T2
WHERE T2.employeeno = T.employeeno
AND startdate >= '2000-01-01'
AND startdate <= '2007-03-30') AS newbenefits
FROM dbo.empBenefits T
WHERE startdate =
(SELECT TOP 1 ([startdate])
FROM empbenefits AS T1
WHERE T1.employeeno = T.employeeno
AND benefitcode = 'HON'
AND startdate >= '2000-01-01'
AND startdate <= '2007-03-30')
GROUP BY employeeno, amount, benefitcode, startdate, enddate, amount ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thank you so much for the reply. Im sorry for that I did not mention
the Version .. I am using SQL2005.
The output I need to see is all employees In one row of data
Ex.
EmployeeNo, Lastname, Firstname, SectionCode, DepartmentCode,
Oldbenefits, NewBenefits, StartDate (Columns are displayed in a Single
Row as shown)
(EmployeeNo Lastname, Firstname, SectionCode, DepartmentCode) comes
from Employees Table
(Oldbenefits, NewBenefits,Startdate ) Comes from empBenefits Table
The second query gets the MIN and MAX Benefits from a row of data with
a specified data range.
I tried this query but it doesnt JOIN in my second Select Statement
seems like LEFT OUTER JOIN DOES NOT WORK
SELECT E.employeeno, datehired, sectioncode, department, lastname,
firstname
FROM dbo.Employees E LEFT OUTER JOIN
dbo.empBenefits T ON E.employeeno = T .employeeno
SELECT employeeno, startdate, amount AS
oldbenefits, enddate, benefitcode,
(SELECT
MAX([amount])
FROM
empbenefits AS T2
WHERE
T2.employeeno = T .employeeno AND startdate >= '2000-01-01' AND
startdate <= '2007-03-30') AS newbenefits
FROM dbo.empBenefits T
WHERE startdate =
(SELECT TOP 1
([startdate])
FROM
empbenefits AS T1
WHERE
T1.employeeno = T .employeeno AND benefitcode <> 'HON' AND startdate >=
'2000-01-01' AND startdate <= '2007-03-30')
GROUP BY employeeno, amount, benefitcode,
startdate, enddate, amount
Thanks,
Heri|||Hi David,
Here are the scripts for employees and empbenefits table hope this
could help thanks again
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[empBenefits]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[empBenefits]
GO
CREATE TABLE [dbo].[empBenefits] (
[EmployeeNo] [int] NOT NULL ,
[BenefitCode] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[Amount] [decimal](10, 2) NULL ,
[Status] [tinyint] NOT NULL ,
[Remarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Employees]
GO
CREATE TABLE [dbo].[Employees] (
[EmployeeNo] [int] NOT NULL ,
[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[MiddleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Department] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SectionCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
) ON [PRIMARY]
GO
