JOIN problem?

Tuesday, February 19, 2013 6:04 AM

0

Hi All,

I have a nightmare of a problem which Ii'm trying to resolve for past 2 days without any success :(

INFO:

I have two tables; 1 contains Employee details and the other, call it 2, info if an employee is being acted as someone else. Example employee A is going on holiday and is being acted as employee B.

Both those tables have pk EmPosKey

My point is to create a query which will return Employee details from table 1 but making sure that if table 1 employee is being acted as someone from table 2, the value in a query will be updates with details of an employee who is acting, from table 2

PROBLEM:

Perhaps I'm lost in JOIN's as I can't understand the fact that the query below works only 99% of times. There are a few exceptions which I cannot grasp logic behind. The ReportsToName_CASE returns a details of an employee from table 1 rather than 2. I believe this points my lack of understanding JOIN fully but based on my boss's anxiety I don't have much time :( Any poits/help would be appreaciated. If my question is unclear, please let me know which part and I'll try give more info.

QUERY:

SELECT

RTS.
EmplPosKey
,

(CASE

WHEN RTS.EmplFullName IN (SELECT EMA.FullName FROM PayCertPrim.dbo.EmplActing EMA)

THEN EMA.ActingFullName

ELSE RTS.EmplFullName

END) AS FullName

,

(CASE

WHEN RTS.ReportsToName IN (SELECT EMA1.FullName FROM PayCertPrim.dbo.EmplActing EMA1)

THEN EMA1.ActingFullName

ELSE RTS.ReportsToName

END) AS ReportsToName_CASE

,

(CASE

WHEN RTS.ReportsToEmail IN (SELECT EMA1.Email FROM PayCertPrim.dbo.EmplActing EMA1)

THEN EMA1.ActingEmail

ELSE RTS.ReportsToEmail

END) AS ReportsToEmail_CASE

FROM PayCertPrim.dbo.RepToStat RTS

LEFT JOIN PayCertPrim.dbo.EmplActing EMA

ON EMA.EmplPosKey = RTS.EmplPosKey

LEFT JOIN PayCertPrim.dbo.EmplActing EMA1

ON EMA1.EmplPosKey = (RTS.ReportsToEmplNo+RTS.ReportsToPosNo)

Thomas Pollack
- Reply
- Quote

All Replies

Tuesday, February 19, 2013 6:12 AM

0

HI !,

Can you provide the sample data for T1 & T2 table along with expected data. So that it would be easy to correct your query.
If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
- Reply
- Quote
Tuesday, February 19, 2013 6:14 AM

0

Your question will be more clear if you provide table definitions and sample data that represent your problem, and we can help you better...
Krishnakumar S
- Reply
- Quote
Tuesday, February 19, 2013 6:15 AM

1

One suspection is due to this
RTS.EmplFullName IN (SELECT EMA.FullName FROM PayCertPrim.dbo.EmplActing EMA)

I think FullName probably is not one unique column in PayCertPrim.dbo.EmplActing. So better to join this table by a PK column directly.
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Tuesday, February 19, 2013 6:35 AM

0

Hi Karthik,

Thanks for your response! Below are examples. Let me know if you need any additional info.

Table 1 Employee Details

Table 2 Employee Acting detail s and Expected Result.

As you can see I not only need to change EmplFullName to the name from Employee Acting but also, ReportsToName, should the name of course exist in the Table 2 - Employee Acting details

Thomas Pollack
- Reply
- Quote

Tuesday, February 19, 2013 6:51 AM

I am still unclear what you're trying to achieve. However can you try the below script...this is just a guess:

SELECT
	RTS.EmplPosKey
	,COALESCE (EMA.ActingFullName, RTS.EmplFullName) AS FullName
	,COALESCE (EMA1.ActingFullName, RTS.ReportsToName) AS ReportsToName_CASE 
	,COALESCE (EMA1.ActingEmail, RTS.ReportsToEmail) AS ReportsToEmail_CASE
FROM 
	PayCertPrim.dbo.RepToStat RTS
LEFT JOIN 
	PayCertPrim.dbo.EmplActing EMA
		ON EMA.EmplPosKey = RTS.EmplPosKey
LEFT JOIN 
	PayCertPrim.dbo.EmplActing EMA1
		ON EMA1.EmplPosKey = (RTS.ReportsToEmplNo+RTS.ReportsToPosNo)

Krishnakumar S

Tuesday, February 19, 2013 6:51 AM

0

Hi Hua Min,

Thanks for your time! Sorry not sure how would you go about it? My logic is perhaps not an SQL logic ;) but I believe this does only check if RTS.EmplFullName does exists in PayCertPrim.dbo.EmplActing. It is a column with unique values ie. not reapiting themselves.
Thomas Pollack
- Reply
- Quote
Tuesday, February 19, 2013 6:56 AM

0

Hi Krishnakumar,

Thanks for that query! Still no joy It works only for 99%.

Let me know what information you may need so it is clear what I'm trying to achieve.

Basically, table RepToStat both columns FullName and ReportsToName has to change if values from those columns exists in the EmplActing table.

Thomas Pollack
- Reply
- Quote

Tuesday, February 19, 2013 7:02 AM

You have alredy LEFT JOINed PayCertPrim.dbo.EmplActing in your query. Why you need SELECT from it again?

Guess it should be like

SELECT
RTS.EmplPosKey 
,CASE WHEN EMA.EmplPosKey is not null // matching EmplActing found 
THEN EMA.ActingFullName
ELSE RTS.EmplFullName
END AS FullName 
,CASE WHEN EMA1.EmplPosKey is not null 
THEN EMA1.ActingFullName
ELSE RTS.ReportsToName
END) AS ReportsToName_CASE 
,CASE WHEN EMA1.EmplPosKey is not null 
THEN EMA1.ActingEmail
ELSE RTS.ReportsToEmail
END) AS ReportsToEmail_CASE
FROM PayCertPrim.dbo.RepToStat RTS
LEFT JOIN PayCertPrim.dbo.EmplActing EMA
ON EMA.EmplPosKey = RTS.EmplPosKey
LEFT JOIN PayCertPrim.dbo.EmplActing EMA1
ON EMA1.EmplPosKey = (RTS.ReportsToEmplNo+RTS.ReportsToPosNo)

Serg

Tuesday, February 19, 2013 7:02 AM

0

Hi Thomas,

Give us some data and table definition that can be copy-paste-and-execute...so that we can test the script and reproduce your problem without guessing....
Krishnakumar S
- Reply
- Quote
Tuesday, February 19, 2013 7:04 AM

1

Hi Hua Min,

Thanks for your time! Sorry not sure how would you go about it? My logic is perhaps not an SQL logic ;) but I believe this does only check if RTS.EmplFullName does exists in PayCertPrim.dbo.EmplActing. It is a column with unique values ie. not reapiting themselves.

Thomas Pollack

Try to adjust it like
...
(CASE
WHEN RTS.EmplFullName IN (SELECT EMA.FullName FROM PayCertPrim.dbo.EmplActing EMA1 where ema.EmplPosKey=ema1.EmplPosKey)
THEN EMA.ActingFullName
ELSE RTS.EmplFullName
END) AS FullName
,
(CASE
WHEN RTS.ReportsToName IN (SELECT EMA1.FullName FROM PayCertPrim.dbo.EmplActing EMA1 where ema.EmplPosKey=ema1.EmplPosKey)
THEN EMA1.ActingFullName
ELSE RTS.ReportsToName
END) AS ReportsToName_CASE
,
(CASE
WHEN RTS.ReportsToEmail IN (SELECT EMA1.Email FROM PayCertPrim.dbo.EmplActing EMA1 where ema.EmplPosKey=ema1.EmplPosKey)
THEN EMA1.ActingEmail
ELSE RTS.ReportsToEmail
END) AS ReportsToEmail_CASE
...
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Tuesday, February 19, 2013 7:10 AM

0

Table 1 - Employee Details

CREATE TABLE [dbo].[RepToStat](
[EmplPosKey] [nvarchar](30) NOT NULL,
[EmplPosNo] [nvarchar](10) NULL,
[EmplFullName] [nvarchar](40) NULL,
[EmplNo] [nvarchar](15) NULL,
[ReportsToPosNo] [nvarchar](10) NULL,
[ReportsToName] [nvarchar](40) NULL,
[ReportsToEmplNo] [nvarchar](15) NULL,
[ReportsToEmail] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[EmplPosKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Table 2 - EmployeeActing Details

CREATE TABLE [dbo].[EmplActing](
[EmplPosKey] [nvarchar](30) NOT NULL,
[ActingEmail] [nvarchar](50) NULL,
[EmployeeCode] [nvarchar](15) NULL,
[FullName] [nvarchar](40) NULL,
[PositionNumber] [nvarchar](15) NULL,
[Email] [nvarchar](50) NULL,
[ActingUptoDate] [date] NULL,
[ActingEmployeeSID] [int] NULL,
[ActingEmployeeCode] [nvarchar](15) NULL,
[ActingFullName] [nvarchar](40) NULL,
[ActingPositionNumber] [nvarchar](15) NULL,
[AcPosKey] [nvarchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[EmplPosKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] Data coming - just need to change details.

Thomas Pollack
- Reply
- Quote
Tuesday, February 19, 2013 7:13 AM

0

Thanks Serg. I need to select from it again as it contains data for two different columns in the RepToStat table.

1st join - needed to sort out If the Full Name exists in the EmplActing table then bring up the Full name from EmplActing table

2nd join, in my idea needed to sort out ReportToname from RepToStat table. If the ReportToname from RepToStat does exists in the EmplActing table then, bring it up while forgetting the value in RepToStat. Does it make sense? I may be working on it for too long hence, my answers are not as thourough as you would expect.
Thomas Pollack
- Reply
- Quote
Tuesday, February 19, 2013 7:14 AM

0

Hi Thomas,

Try to adjust it in the way I mentioned above.
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Tuesday, February 19, 2013 7:23 AM

0

Hi Thomas,

Try to adjust it in the way I mentioned above.

Many Thanks & Best Regards, Hua Min

Hi Huan Min,

Thank's for tha! :) I did and it still has the same problem as original. I'm creating the tables and I need to manage their relationship (even though my SQL skills are intermediate :) hence, perhaps a suggestion to change the table structure. I'm developign a query to populate tables which creating script is pasted above.
Thomas Pollack
- Reply
- Quote
Tuesday, February 19, 2013 7:27 AM

0

Hi Thomas,

Give us some data and table definition that can be copy-paste-and-execute...so that we can test the script and reproduce your problem without guessing....

Krishnakumar S

Hi Krishnakumar, the table def has been pasted below. I'm trying to write a quick script to populate those table.
Thomas Pollack
- Reply
- Quote

Tuesday, February 19, 2013 7:41 AM

Hi Thomas,

Give us some data and table definition that can be copy-paste-and-execute...so that we can test the script and reproduce your problem without guessing....

Krishnakumar S

Hi Krishnakumar, the table def has been pasted below. I'm trying to write a quick script to populate those table.

Thomas Pollack

Table RepToPos

EmplPosKey	EmplPosNo	EmplFullName	EmplNo	ReportsToPosNo	ReportsToName	ReportsToEmplNo	ReportsToEmail
96400072618	72618	Brook, Ten	9640	3185	Lewis, Ann	10621	ann.lewis@123
376312002378	2378	Gan, Marg	376312	2369	Jami Jill	418214	jill.jami@123
418214002369	2369	Jami, Jill	418214	2046842	Har, Elis	6232	liz.har@123
7089232110787	2110787	Jen, Greg	708923	2369	Jami, Jill	418214	jill.jami@123

Thomas Pollack

Tuesday, February 19, 2013 7:46 AM

Hi Thomas,

Give us some data and table definition that can be copy-paste-and-execute...so that we can test the script and reproduce your problem without guessing....

Krishnakumar S

Hi Krishnakumar, the table def has been pasted below. I'm trying to write a quick script to populate those table.

Thomas Pollack

Table RepToPos

EmplPosKey EmplPosNo EmplFullName EmplNo ReportsToPosNo ReportsToName ReportsToEmplNo ReportsToEmail

96400072618 72618 Brook, Ten 9640 3185 Lewis, Ann 10621 ann.lewis@123

376312002378 2378 Gan, Marg 376312 2369 Jami Jill 418214 jill.jami@123

418214002369 2369 Jami, Jill 418214 2046842 Har, Elis 6232 liz.har@123

7089232110787 2110787 Jen, Greg 708923 2369 Jami, Jill 418214 jill.jami@123

Thomas Pollack

EmplActing Table

EmplPosKey	ActingEmail	EmployeeCode	FullName	PositionNumber	Email	ActingUptoDate	ActingEmployeeSID	ActingEmployeeCode	ActingFullName	ActingPositionNumber	AcPosKey
11399003448	darshi.ganeson@123	11399	Do, Nhi Y T	3448	nhi.do@123	31/07/2013	1745	6958	Ganeson-Oats, Darshi	2386	6958002386
11411003203	kath.clark@123	11411	Thomson, Kathryn	3203	Kath.Thom@123	20/03/2013	5414	9094	Clark, Kath	2801	9094002801
27595002534	chris.hugh@123	27595	Goldsworthy, Janine M	2534	jani.golds@123	5/04/2013	2934	9787	Hugh, Chris	3193	9787003193
37849002532	jani.golds@123	37849	Porter, Philip J	2532	phil.porter@123	5/04/2013	6299	27595	Golds, Jani	2534	27595002534
4.18214E+11	mel.sore@123	418214	Jamieson, Jillian	2369	jill.jamie@123	9/08/2013	1471	10061	Sor, Mel	2772	10061002772

Thomas Pollack

Tuesday, February 19, 2013 8:01 AM

Part of your nightmare is because of a failure to recognize the primary key. Rest of it is due to arranging the information in a disorganized way.

EmplNo + '00' + EmplPosNo = EmplPosKey

The way you have named and ordered the columns in the Excel screenshots, they do not have any logical flow. Otherwise the requirement would have been simple to explain and resolve.

Please try this query -

SELECT    RTS.EmplPosKey                                  ,
          RTS.EmplPosNo                                   ,
          COALESCE(EMA.ActingFullName, RTS.EmplFullName)  ,
          RTS.EmplNo                                      ,
          RTS.ReportsToPosNo                              ,
          COALESCE(EMA.ActingFullName, RTS.ReportsToName) ,
          RTS.ReportsToEmplNo                             ,
          COALESCE(EMA.ActingEmail, RTS.ReportsToEmail)
FROM      PayCertPrim.dbo.RepToStat            AS RTS
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA
          ON        RTS.EmplPosNo = EMA.PositionNumber
          AND       RTS.EmplNo    = EMA.EmployeeCode

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Tuesday, February 19, 2013 8:10 AM

0
Part of your nightmare is because of a failure to recognize the primary key. Rest of it is due to arranging the information in a disorganized way.

EmplNo + '00' + EmplPosNo = EmplPosKey

The way you have named and ordered the columns in the Excel screenshots, they do not have any logical flow. Otherwise the requirement would have been simple to explain and resolve.

Please try this query -
```
SELECT    RTS.EmplPosKey                                  ,
          RTS.EmplPosNo                                   ,
          COALESCE(EMA.ActingFullName, RTS.EmplFullName)  ,
          RTS.EmplNo                                      ,
          RTS.ReportsToPosNo                              ,
          COALESCE(EMA.ActingFullName, RTS.ReportsToName) ,
          RTS.ReportsToEmplNo                             ,
          COALESCE(EMA.ActingEmail, RTS.ReportsToEmail)
FROM      PayCertPrim.dbo.RepToStat            AS RTS
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA
          ON        RTS.EmplPosNo = EMA.PositionNumber
          AND       RTS.EmplNo    = EMA.EmployeeCode
```
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)
Hi Aalam,

Thank you for your reply. Much apprecaited!

Your query does help however, doesn't work on my db. With this approach, 1% of data is correct while the rest shows erratic value.

Based on your explanation, I'm blaming the table structure but quite frankly, have no idea how to fix it. Would it be possible, in anyway, for me to sent you the data within table or do a skype, remote session to show you what I'm working on?

Thomas Pollack
- Reply
- Quote

Tuesday, February 19, 2013 8:46 AM

Could you try -

SELECT    RTS.EmplPosKey                                  ,
          RTS.EmplPosNo                                   ,
          COALESCE(EMA1.ActingFullName, RTS.EmplFullName) AS EmplFullName  ,
          RTS.EmplNo                                      ,
          RTS.ReportsToPosNo                              ,
          COALESCE(EMA2.ActingFullName, RTS.ReportsToName) AS ReportsToName ,
          RTS.ReportsToEmplNo                             ,
          COALESCE(EMA2.ActingEmail, RTS.ReportsToEmail) AS ReportsToEmail
FROM      PayCertPrim.dbo.RepToStat            AS RTS
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA1
          ON        RTS.EmplPosNo = EMA1.PositionNumber
          AND       RTS.EmplNo    = EMA1.EmployeeCode
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA2
          ON        RTS.ReportsPosNo = EMA2.PositionNumber
          AND       RTS.ReportsToEmplNo    = EMA2.EmployeeCode

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Marked As Answer by TPollack Wednesday, February 20, 2013 3:20 AM

Wednesday, February 20, 2013 2:08 AM

Could you try -

SELECT    RTS.EmplPosKey                                  ,
          RTS.EmplPosNo                                   ,
          COALESCE(EMA1.ActingFullName, RTS.EmplFullName) AS EmplFullName  ,
          RTS.EmplNo                                      ,
          RTS.ReportsToPosNo                              ,
          COALESCE(EMA2.ActingFullName, RTS.ReportsToName) AS ReportsToName ,
          RTS.ReportsToEmplNo                             ,
          COALESCE(EMA2.ActingEmail, RTS.ReportsToEmail) AS ReportsToEmail
FROM      PayCertPrim.dbo.RepToStat            AS RTS
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA1
          ON        RTS.EmplPosNo = EMA1.PositionNumber
          AND       RTS.EmplNo    = EMA1.EmployeeCode
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA2
          ON        RTS.ReportsPosNo = EMA2.PositionNumber
          AND       RTS.ReportsToEmplNo    = EMA2.EmployeeCode

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

BIG thanks Aalam!!

This time 99% records were correct and only 1% was not. It changes columns EmplFullName with RTS.EmplFullName. it also manages ReportsToName but it leaves 1% of records with no change to it, ReportsToName. The ReportsToName remains the same as EmplFullName.

I think, based on yours and others comments, the problem relies on the relation between the table?

Thomas Pollack

Wednesday, February 20, 2013 3:20 AM

Could you try -

SELECT    RTS.EmplPosKey                                  ,
          RTS.EmplPosNo                                   ,
          COALESCE(EMA1.ActingFullName, RTS.EmplFullName) AS EmplFullName  ,
          RTS.EmplNo                                      ,
          RTS.ReportsToPosNo                              ,
          COALESCE(EMA2.ActingFullName, RTS.ReportsToName) AS ReportsToName ,
          RTS.ReportsToEmplNo                             ,
          COALESCE(EMA2.ActingEmail, RTS.ReportsToEmail) AS ReportsToEmail
FROM      PayCertPrim.dbo.RepToStat            AS RTS
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA1
          ON        RTS.EmplPosNo = EMA1.PositionNumber
          AND       RTS.EmplNo    = EMA1.EmployeeCode
          LEFT JOIN PayCertPrim.dbo.EmplActing AS EMA2
          ON        RTS.ReportsPosNo = EMA2.PositionNumber
          AND       RTS.ReportsToEmplNo    = EMA2.EmployeeCode

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Thanks again for your points Aalam, and All.

I think I have solved the problem. It is not even close to a good solution but with the time constrain I cannot afford to test other ways to resolve it.

I've created a seperate table, a copy of EmplActing with pk as ReportingToEmplNo and its PosNo. Joined your query Aalam (hence I mark your point as answer) with the new table and it works.

Thomas Pollack

JOIN problem?

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?