Using Join - SQL Server Forums

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)

Using Join

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 11:07:32

I am trying to join two tables around a common field (employeenumber) using join and I'm having a hard time getting that to work.

I have tables scratchpad1 and scratchpad2 and I'm trying to do the following

select [scratchpad1].name, [scratchpad2].exceptiondate, sum(duration)

and then I'm trying to join scratchpad1 and 2 on the employeenumber

and then insert employeenumber, name, exceptiondate, duration into scratchpad3.

Can anyone offer any help with this?

Thank you

Doug

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 11:33:13

Ok, selecting from two tables using join isn't a problem.
But what do you want to sum()?
Can you give DDL, example data, wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 12:15:50

Webfred,

The sum isn't absolutely necessary. I can do that in another query, but I'm trying to combine a few queries where I can.

Some example data is as follows:

This is from Scratchpad1:
Name Employeenumber Date Login Ontime
Cametria Alexander 8451 5/1/2010 9:37:57 PM 7.055783333 3.34995

This is from Scratchpad2:
Employeenumber Exceptiondate Starttime EndTime Code Duration
8245 11/1/2010 1/1/1900 10:00:00 AM 1/1/1900 10:30:00 AM Approved Technical Reason 30

All fields are varchar (deafult values) except for
Scratchpad1
date which is datetime
login which is decimal
ontime which is decimal

Scratchpad2
exceptiondate is datetime
starttime is datetime
endtime is datetime

I hope that helps.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 12:15:51

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 13:35:11

This join brings only rows if there are matching rows in the joined table otherwise use a left join and see what happens.
SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, s2.Duration
FROM Scratchpad1 AS s1
JOIN Scratchpad2 AS s2 ON s1.Employeenumber = s2.Employeenumber

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 14:29:27

Webfred,

Here's how I modified the query:

SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, s2.Duration
FROM Scratchpad1 AS s1
inner JOIN Scratchpad2 AS s2 ON s1.Employeenumber = s2.Employeenumber
where exceptiondate between '11/1/2010' and '11/15/2010'
group by s1.[Name], s1.Employeenumber, s2.Exceptiondate, s2.Duration

But what I'd also like to do here is to sum up the results from the day. Here is the results from the query that I'd like to sum:

8247 Eric Edwards 2010-11-15 00:00:00.000 10
8247 Eric Edwards 2010-11-15 00:00:00.000 60

These entries are for the same day so I'd like to be able to present the user one result of

8247 Eric Edwards 2010-11-15 70

I'm not sure where to put the sum in here to produce the results I'm needing.

Thank you

Doug

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 15:24:03

Take the duration out of the group by and just do sum(duration) in your select list

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 15:56:44

web,

here's the result I get back for this query:

2010-11-14 00:00:00.000 Christy Clayton 8455 20700

The duration is showing 20700 for that day when the result I run from this query:

select employeenumber, exceptiondate, sum(duration) as totalminutes
from scratchpad2
where exceptiondate between '11/1/2010' and '11/15/2010'
group by employeenumber, exceptiondate

shows the sum(duration) as 60.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 16:12:32

Sorry I don't know exactly what you mean.
What gives
select employeenumber, exceptiondate, duration
from scratchpad2
where exceptiondate between '11/1/2010' and '11/15/2010'
and employeenumber = 8455

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 16:16:14

This is a query that i'm running that doesnt join the two tables but produces the sum of the duration. The reason that I'm trying to join them is because I'd like to be able to pass both the name and employeenumber back in my result. As you can see from my query, it doesnt return any information between the tables that are common. Which I need.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-04 : 12:39:38

Fred was my explanation not clear enough? I can try to explain it better if it's still unclear to you.

Thank you

Doug

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 12:47:29

quote:
Originally posted by dougancil

web,

here's the result I get back for this query:

2010-11-14 00:00:00.000 Christy Clayton 8455 20700

The duration is showing 20700 for that day when the result I run from this query:

select employeenumber, exceptiondate, sum(duration) as totalminutes
from scratchpad2
where exceptiondate between '11/1/2010' and '11/15/2010'
group by employeenumber, exceptiondate

shows the sum(duration) as 60.

I will try to tell you what isn't clear to me.
A:
You have executed this:

select employeenumber, exceptiondate, sum(duration) as totalminutes
from scratchpad2
where exceptiondate between '11/1/2010' and '11/15/2010'
group by employeenumber, exceptiondate

B:
You say a line from the result set is this:

2010-11-14 00:00:00.000	Christy Clayton	8455	20700

C:
What I don't know is:
Do you mean 20700 isn't correct and should be 60 or what is the problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-04 : 13:38:42

That is correct, when I execute this query

SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, sum(duration)
FROM Scratchpad1 AS s1
inner JOIN Scratchpad2 AS s2 ON s1.Employeenumber = s2.Employeenumber
where exceptiondate between '11/1/2010' and '11/15/2010'
group by s1.[Name], s1.Employeenumber, s2.Exceptiondate

Results: 8245 Yvonne Becerra 2010-11-01 00:00:00.000 9780

it shows that the value for the duration as being incorrect, BUT if I run my simplified query (without the join clause):

select employeenumber, exceptiondate, sum(duration) as totalminutes
from scratchpad2
where exceptiondate between '11/1/2010' and '11/15/2010'
group by employeenumber, exceptiondate

Results: 8245 2010-11-01 00:00:00.000 30

it shows the correct sum for the duration. I hope that makes more sense by posting the results from both queries.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:11:55

Maybe this:
SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, sum(duration)
FROM Scratchpad1 AS s1
inner JOIN Scratchpad2 AS s2 ON s1.Employeenumber = s2.Employeenumber
and exceptiondate between '11/1/2010' and '11/15/2010'

group by s1.[Name], s1.Employeenumber, s2.Exceptiondate

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-04 : 15:21:36

Webfred,

It shows the same result as before:

Results: 8245 Yvonne Becerra 2010-11-01 00:00:00.000 9780

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:32:57

Maybe there are duplicates in Scratchpad1 or whatever the reason is...
But this should work:
SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, s2.totalminutes
FROM
(select distinct Employeenumber,[Name] from Scratchpad1) AS s1
inner JOIN
(select employeenumber, exceptiondate, sum(duration) as totalminutes
from scratchpad2
where exceptiondate between '11/1/2010' and '11/15/2010'
group by employeenumber, exceptiondate) as s2
ON s1.Employeenumber = s2.Employeenumber

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-04 : 15:39:46

Webfred,

that did the trick. Thank you.

Doug

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:41:14

It was about time