Left Join, Right Join, Inner Join, or outer join ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Left Join, Right Join, Inner Join, or outer join ?

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 06:00:13

Dear experts,

I got a database with the following tables

1. Employee(employeeId GUID (PK), [Name], JoinDate, Designation)
2. EmployeeAppraisal(apprisalID GUID (PK), Type, Period, Score, modifiedDate, Submitted)
this is one to many relationship (one employee has many employeeAppraisals)
3. Company (companyID (PK), CompanyName)
4. Department(departmentID (PK), departmentName)
5. EmployeeDepartmentCompanyHistory(employeeId (FK), DepartmentId (FK), CompanyID (FK), modifiedDate)
this is a one employee multiple employeedepartmentcompanyhistory relationship.

I want to get

EmployeeName, CompanyName, DepartmentName, Designation, Type (Appraisal), Period, Score

but with the following restrictions:

There should be only 1 employeeName per line. i.e. it will only display the latest company, department, type, period, and score and ignore the rest.

I have no difficulty getting the information with multiple results for 1 employee, but is stumped

on how to get only the latest result for each employee.

Any hints or help is very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 06:08:54

We need some sample data from your tables to understand how data is. can you provide it please? ALso specify how Appraisal table is linked to Employe. i cant see any linking columns b/w them.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 06:11:44

Visakh16. Sorry, I forgot to type the following

EmployeeAppraisal includes (EmployeeID as one column).

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 06:12:43

I'd try something like....


SELECT
	e.<stuff>
	, ea.<stuff>
FROM
	employee e

	JOIN (
		SELECT
			[employeeId] AS empId
			, MAX([period]) AS lastPeriod
		FROM
			employeeAppraisal
		GROUP BY
			[employeeId]
	)
	lEa ON lEa.[empId] = e.[employeeId]

	JOIN employeeAppraisal ea ON
		ea.[employeeId] = lEa.[empId]
		AND ea.[period] = lEa.[lastPeriod]

That is assuming that you employeeAppraisal table does have an [employeeId] column or similar because you haven't specified this. I assume this must be how the table is set up. It also assumes that an employee can't have more than 1 appraisal in the same exact period.

And then do something similar for the other 1 - many relationship tables.

All the best.

-------------
Charlie

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 06:21:03

Same data
Employee( 1234, 'Eugene' , '16 Jun 2008', 'Intern')
EmployeeAppraisal (2, 'annual appraisal', 'Jul 2008 - Sep 2008', 83, '14 Sep 2008', 1, 1234 (employeeID))
EmployeeAppraisal (3, 'annual appraisal', 'Apr 2008 - May 2008', 64, '16 Mar 2008', 1, 1234 (employeeID))
Company (1, 'ABC Corp' )
Department(1, 'Sales')
Department(2, 'IT')
EmployeeDepartmentCompanyHistory(1234, 2, 1, '20 Mar 2008')
EmployeeDepartmentCompanyHistory(1234, 1, 1, '16 Sep 2008')

Data I want

EmployeeName (Eugene)
CompanyName (ABC Corp)
DepartmentName (Sales)
Designation (Intern)
Type (annual Appraisal)
Period (Jul 2008 - Sep 2008)
Score (83)

only. hope this is clear..

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 06:25:49

transact Charlie, thank you for post, I am a bit lost at your explanation. Let me try and see if I could try to construct the query based on your feedback. :D

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 06:34:19

I thought you said that EmployeeAppraisal had a GUID as primary key? (they look like xgvja-xcvs-sfgag-zva34-fasf and are guaranteed to be unique in your entire database)

If it has a running identity column instead of this then it makes it easier.

try this.


SELECT
	e.[name]
	, c.[companyName]
	, d.[departmentName]
	, e.[designation]
	, ea.[type]
	, ea.[period]
	, ea.[score]
FROM
	employee e

	JOIN (
		SELECT
			[employeeId] AS empId
			, MAX([modifiedDate]) AS latestDate
		FROM
			employeeDepartmentHistory
		GROUP BY
			[employeeId]
		)
		hEdh ON hEdh.[empId] = e.[employeeId]

	JOIN employeeDepartmentHistort edh ON
		edh.[employeeId] = hEdh.[empId]
		AND edh.[modifiedDate] = hEdh.[latestDate]

	JOIN department d ON d.[departmentId] = edh.[departmentId]
	JOIN company c ON c.[companyId] = edh.[companyId]

	JOIN (
		SELECT
			[employeeId] AS empId
			, MAX([appraisalId]) AS eaId
		FROM
			employeeAppraisal
		GROUP BY
			[employeeID]
		)
		hEa ON hEa.[empId] = e.[Id]

	JOIN employeeAppraisal ea ON ea.[appraisalId] = hEa.[eaId]

There could be typos because I haven't bothered making up your tables.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 06:36:37

Sorry I just noticed that your appraisalId's seem to be backwards in regards to period dates.

appraisalId 2 seems to corrospond to the latest date and 3 seems to be the early date.

I'll change my example

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 06:38:05

try this instead:


SELECT
	e.[name]
	, c.[companyName]
	, d.[departmentName]
	, e.[designation]
	, ea.[type]
	, ea.[period]
	, ea.[score]
FROM
	employee e

	JOIN (
		SELECT
			[employeeId] AS empId
			, MAX([modifiedDate]) AS latestDate
		FROM
			employeeDepartmentHistory
		GROUP BY
			[employeeId]
		)
		hEdh ON hEdh.[empId] = e.[employeeId]

	JOIN employeeDepartmentHistort edh ON
		edh.[employeeId] = hEdh.[empId]
		AND edh.[modifiedDate] = hEdh.[latestDate]

	JOIN department d ON d.[departmentId] = edh.[departmentId]
	JOIN company c ON c.[companyId] = edh.[companyId]

	JOIN (
		SELECT
			[employeeId] AS empId
			, MAX([modifiedDate]) AS latestDate
		FROM
			employeeAppraisal
		GROUP BY
			[employeeID]
		)
		hEa ON hEa.[empId] = e.[employeeId]

	JOIN employeeAppraisal ea ON
		ea.[employeeId] = hEa.[empId]
		AND ea.[modifiedDate] = hEa.[latestDate]

-------------
Charlie

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 06:39:48

yes, all PK are GUID, but for purpose of making sample data for Viskah16, I give some sample data.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 06:41:05

latest example doesn't use any intrinsic ordering of pk's so should work.

-------------
Charlie

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 06:46:18

Thank you Charlie. This works perfectly for the example above.

After analyzing the sql query, I understand how to construct the actual (slightly more complex) query for my homework now.

things learnt today
"MAX(modified Date)" to get record with latest date. :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources