Error on join for new employee id

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 2000 Forums

Transact-SQL (2000)

Error on join for new employee id

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-09-24 : 09:31:40

I've a Table raw_data which jons to phone_data table on the date and the empl_id when i test this join it works fine, the table phone_data joins to a teams table on team number when i test this join between the phone_data and teams table it work fine.

When i join the raw_data to phone_data table on the date and the empl_id and then the phone_data table to the teams on tem number table i get 0 result back, this only happens for 1 employee id
when i test it with a different employee it works fine any idea why this would be the case.

I've check that the employee id is correct

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-09-24 : 09:34:24

what kind of join are you doing?
can you post your sql query?

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-09-24 : 10:09:13

Here is the script, as i said i just have a problem with 1 employee number where it won't return back any data, when i test it with the other employee id's it works.

SELECT 1 AS esgl1_ind, Phone_data.[date], Teams.team_number, Teams.organisation, Phone_data.empl_id
FROM dbo.tbl_asp_ext_hist Phone_data INNER JOIN
dbo.tbl_teams Teams ON Phone_data.team_no = Teams.team_number INNER JOIN
dbo.tbl_raw_out_call_actions Raw_data ON Phone_data.empl_id = Raw_data.empl_id AND Phone_data.[date] = Raw_data.customer_event_dt
GROUP BY Phone_data.[date], Teams.team_number, Phone_data.empl_id, Teams.organisation
HAVING (Phone_data.empl_id = '77348') AND (Teams.organisation = N'ESG1')

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-25 : 15:54:10

What's the data look like for that one employee ID?

Maybe the dates are in different formats, or maybe they simply don't equal.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 16:01:45

This is your query. I have moved the HAVING part to WHERE statements.

SELECT		1 AS esgl1_ind,
		Phone_data.[date],
		Teams.team_number,
		Teams.organisation,
		Phone_data.empl_id
FROM		dbo.tbl_asp_ext_hist as Phone_data
INNER JOIN	dbo.tbl_teams as Teams ON Phone_data.team_no = Teams.team_number
INNER JOIN	dbo.tbl_raw_out_call_actions as Raw_data ON Phone_data.empl_id = Raw_data.empl_id
			AND Phone_data.[date] = Raw_data.customer_event_dt
WHERE		Phone_data.empl_id = '77348'
		AND Teams.organisation = N'ESG1'
GROUP BY	Phone_data.[date],
		Teams.team_number,
		Phone_data.empl_id,
		Teams.organisation

As you can see, you also JOIN over date column.
Maybe this employee number has time information in the column, so that the JOIN is not valid?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources