Assistance needed with a complex join/group

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

SQL Server 2005 Forums

Transact-SQL (2005)

Assistance needed with a complex join/group

Author

Topic

kellynoelle
Starting Member

3 Posts

Posted - 2007-10-24 : 13:46:43

I have a query that is quite frankly kicking my butt and I could use some help. I'm doing something wrong with the joins and group by clause (i've read elsewhere online that the group by clause is mostly misused and I tried to edit the select part with no luck). Here is the query as it looks now (a work in progress):

SELECT top 250 EMP_PLANS.EmpPlanID, PerfPlanTypeID, FirstName,
MI, LastName, SAC, Grade, POS_NUM, Title, OCC_SERIES, Organization, FinalAppraisalDate,
(Select TOp 1 FinalLevel FROM EMP_ELEMENTRATINGS
Where PLAN_ELEMENTS.ElementNum = 1) as Elem1,
(Select top 1 FinalLevel FROM EMP_ELEMENTRATINGS
Where PLAN_ELEMENTS.ElementNum = 2 AND EMP_ELEMENTRATINGS.EMPPLANID = EMP_PLANS.EMPPLANID) as Elem2,
(Select top 1 FinalLevel FROM EMP_ELEMENTRATINGS
Where PLAN_ELEMENTS.ElementNum = 3 AND EMP_ELEMENTRATINGS.EMPPLANID = EMP_PLANS.EMPPLANID) as Elem3,
(Select top 1 FinalLevel FROM EMP_ELEMENTRATINGS
Where PLAN_ELEMENTS.ElementNum = 4 AND EMP_ELEMENTRATINGS.EMPPLANID = EMP_PLANS.EMPPLANID) as Elem4,
(Select top 1 FinalLevel FROM EMP_ELEMENTRATINGS
Where PLAN_ELEMENTS.ElementNum = 5 AND EMP_ELEMENTRATINGS.EMPPLANID = EMP_PLANS.EMPPLANID) as Elem5,
(Select top 1 FinalLevel FROM EMP_ELEMENTRATINGS
Where PLAN_ELEMENTS.ElementNum = 6 AND EMP_ELEMENTRATINGS.EMPPLANID = EMP_PLANS.EMPPLANID) as Elem6
FROM EMP_PLANS
INNER JOIN EMP_ELEMENTRATINGS on EMP_PLANS.EmpPlanID = EMP_ELEMENTRATINGS.EmpPlanID
LEFT JOIN PLAN_ELEMENTS on PLAN_ELEMENTS.ElementID = EMP_ELEMENTRATINGS.ElementID
WHERE Status = 'F' and ApprYear = '2007'
Group by EMP_PLANS.EmpPlanID, SSN, PerfPlanTypeID, FirstName, MI, LastName, SAC, Grade, POS_NUM, Title, OCC_SERIES, Organization,
FinalAppraisalDate, ElementNum

This query returns a seperate record/row for each Element. I'd like for the results to show all element ratings in one record. Example: Jane Doe XXX XXX 3 3 3 3 NULL NULL (for an employee with only 4 elements).

In the last part of the select (with the nested select statements for Elem1 - 6) - I had to do a "top 1" else I received a message that more than one record was returned.

This probably isn't making alot of sense, I apologize. Please ask any questions.

Thanks for taking the time to read this.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-24 : 16:05:36

kellynoelle,

You're right. It isn't making a lot of sense. :-)

Try posting your table schema, sample data, and the desired output of your query. That'll give someone enough information to try and help.

kellynoelle
Starting Member

3 Posts

Posted - 2007-10-25 : 07:29:00

This is a little long but here it goes:

EMP_PLANS
Name Type Length Allow Null? Relationship
EmpPlanID int 4 NO PK; FK w/EMP_ELEMENTRATINGS
FirstName varchar 20 NO
MI char 1 YES
LastName varchar 25 NO
SAC varchar 10 NO
Organization varchar 50 YES
Title varchar 50 YES
POS_NUM varchar 50 YES
OCC_SERIES char 10 YES
Grade char 10 YES
ApprYear int 4 YES
ApprBeginDate smalldatetime 4 YES
PerfPlanTypeID char 1 YES FK w/PLAN_TYPES
Status char 1 YES
RatingID char 10 YES FK w/PLAN_RATINGS
FinalAppraisalDate smalldatetime 4 YES

EMP_ELEMENTRATINGS
Name Type Length Allow Null? Relationship
EmpPlanID int 4 NO PK; FK w/EMP_PLANS
ElementID int 4 NO PK; FK w/PLAN_ELEMENTS
PerfDiscussion text 16 YES
FinalDiscussion text 16 YES
OptDiscussion text 16 YES
FinalLevel int 4 YES

PLAN_ELEMENTS
Name Type Length Allow Null? Relationship
ElementID int 4 NO PK; FK w/EMP_ELEMENTRATINGS
PlanTypeID char 1 NO
ElementNum int 4 NO
ElementName varchar 75 NO
ElementDesc text 16 NO

PLAN_RATINGS
Name Type Length Allow Null? Relationship
RatingID int 4 NO PK; FK w/EMP_PLANS
Numelements int 4 YES
RatingAvg numeric 5 YES
RatingSummary varchar 25 YES
OverallSummary int 4 YES

PLAN_TYPES
Name Type Length Allow Null? Relationship
PlanTypeID char 1 NO PK; FK w/EMP_PLANS
PlanTypeName varchar 75 NO
NumElements int 4 NO

Sample Data would be:

EMP_PLANS
EmpPlanID 999
FirstName John
MI A
LastName Smith
SAC S32e4r5
Organization OFFICE OF FINANCE
Title Deputy Assistant
POS_NUM 9999999
OCC_SERIES 00000
GRADE GS-11
APPRYEAR 2007
ApprBeginDate 10/01/2006
PerfPlanTypeID 1
Status F
RatingID 12
FinalAppraisalDate 10/12/2007

EMP_ELEMENTRATINGS
EMPPLANID 999
ElementID 6
PerfDiscussion longtext
FinalDiscussion longtext
OptDiscussion longtext
FinalLevel 5

EMPPLANID 999
ElementID 7
PerfDiscussion longtext
FinalDiscussion longtext
OptDiscussion longtext
FinalLevel 3

EMPPLANID 999
ElementID 8
PerfDiscussion longtext
FinalDiscussion longtext
OptDiscussion longtext
FinalLevel 3

EMPPLANID 999
ElementID 9
PerfDiscussion longtext
FinalDiscussion longtext
OptDiscussion longtext
FinalLevel 5

PLAN_ELEMENTS
ElementID 6
PlanTypeID 1
ElementNum 1
ElementName ElemOne
ElementDesc longtext

ElementID 7
PlanTypeID 1
ElementNum 2
ElementName ElemTwo
ElementDesc longtext

ElementID 8
PlanTypeID 1
ElementNum 3
ElementName ElemThree
ElementDesc longtext

ElementID 9
PlanTypeID 1
ElementNum 4
ElementName ElemFour
ElementDesc longtext

PLAN_RATINGS
RatingID 12
Numelements 4
RatingAvg 4.0
RatingSummary 3
OverallSummary 3

Plan_Types
PlanTypeID 1
PlanTypeName Non-Manager
NumElements 4

Desired Output:
EmpPlanID, PerfPlanTypeID, FirstName, MI, LastName, SAC, Grade, POS_NUM, Title, OCC_Series, Organization, FinalAppraisalDate, ElemOne Final Rating, ElemTwo Final Rating, ElemThree FinalRating, ElemFour Final Rating, RatingAvg, RatingSummary, OverallSummary

From Example that would look like:
999, John, A, Smith, S32e4r5, GS-11, 9999999, 00000, Deputy Assistant, 00000, OFFICE OF FINANCE, 10/12/2007, 5, 3, 3, 5, 4.0, 3, 3

Thanks again for taking the time to read this!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 08:19:03

I would try to rewrite second half of query with PIVOT operator.

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

kellynoelle
Starting Member

3 Posts

Posted - 2007-10-25 : 08:22:43

I've never used PIVOT - could you help explain how to use it for this type of query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources