Joining Three Tables - 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 2005 Forums

Transact-SQL (2005)

Joining Three Tables

Author

Topic

punkle
Starting Member

3 Posts

Posted - 2009-01-28 : 12:11:24

Hi,

I am relatively new to SQL and I am trying to create a query on three tables.

The first table contains a list of work hour templates assigned to staff members.

The second table shows these staff members and the dates that they worked per day along with their actual work hours for that day.

The third table contains the list of work hour templates with the hours that an allocated staff member is expected to work.

I am looking to create a query that gives a report for a given staff member and a given date range to show the expected work hours versus the actual worked hours.

I have managed to get the information I need from each individual table but it is when it comes to linking the tables together that I am running into problems. I have tried to used subqueries.

I do not expect anyone to solve this problem for me, but if someone could point me in the right direction I would really appreciate it. Am I in the wrong ball park using a subquery for this?

Brian

mapidea
Posting Yak Master

124 Posts

Posted - 2009-01-28 : 12:15:36

The first table contains a list of work hour templates assigned to staff members.

Please explain what do you mean by "work hour templates"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:19:44

you need to basically apply aggregate over your actual working hours and also hours expected to work for each of staff i.e GROUP BY staff field and take SUM() of both the hours column from two tables.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-28 : 12:23:27

quote:
I have managed to get the information I need from each individual table but it is when it comes to linking the tables together that I am running into problems. I have tried to used subqueries.

If you are trying to 'link' tables together you may wish to use JOIN clauses. Google SQL JOIN and you can learn how to use it. Basically it will 'link' your tables together. It really all depends on what information you need to retrieve. You may find that you need to also apply aggregate functions as visakh16 suggested

An example might be: Retrieve all Persons who have a Sales Order in some database.


SELECT Persons.LastName, Persons.FirstName, SalesOrders.OrderNo
FROM Persons
INNER JOIN SalesOrders
ON Persons.P_Id=SalesOrders.P_Id
ORDER BY Persons.LastName

Here is a link to help get you started with the basics.
[url]http://www.w3schools.com/Sql/sql_join.asp[/url]

r&r

punkle
Starting Member

3 Posts

Posted - 2009-01-28 : 14:15:18

I dont think I explained myself properly in the first post so I will expand with a description of the data that is in each table.

The first table, lets call it staffMember contains two fields:

staffID
workHourTemplateID

essentially this table contains a relationship between the staff members and their expected work Hours held in the workHourTemplateTable.

The second table, timeSheetCalender:

staffID
dateWorked
inTime
outTime

this table contains the dates that each worker worked as well as the times that the staff member arrived and the time that they left.

And the third table, workHourTemplateTable

workHourTemplateID
inTime
outTime

this table defines the work hours that a worker is expected to arrive and leave per day depending on the template assigned.

what I need is to get a query that returns a total of how many hours a staff member worked between two specified dates and also the amount of hours that the staff member was expected to work.

So what I would need is something like this

SELECT staffID, (SELECT SUM(EACH DAYs WORK HOURS BETWEEN <<DATE FROM>> AND <<DATE TOO>>), (SELECT SUM(EACH DAYS EXPECTED WORK HOURS BETWEEN <<DATE>> AND <<DATE>>) FROM staffMember, timeSheetCalender, workHourTemplateTable;

mapidea
Posting Yak Master

124 Posts

Posted - 2009-01-29 : 01:06:18

Select
a.StaffId,
SUM(Datediff(hour,b.outtime, b.intime),
SUM(Datediff(hour,c.outtime, c.intime)
From StaffMember As a
Inner Join timeSheetCalender As b ON a.StaffId=b.StaffId
Inner Join workHourTemplateTable As c ON c.workHourTemplateID=a.workHourTemplateID

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-29 : 01:12:42

slight modification added groupby

quote:
Originally posted by mapidea

Select
a.StaffId,
SUM(Datediff(hour,b.outtime, b.intime),
SUM(Datediff(hour,c.outtime, c.intime)
From StaffMember As a
Inner Join timeSheetCalender As b ON a.StaffId=b.StaffId
Inner Join workHourTemplateTable As c ON c.workHourTemplateID=a.workHourTemplateID
group by a.staffid

punkle
Starting Member

3 Posts

Posted - 2009-01-29 : 09:38:46

Thanks everybody for all your help. The statement you have given me seems to be what I am looking for with the exeception of one part. The relationship between staffID in the staffMember and timeSheetCalender tables do not have a one to one relationship. The staffMember table also has a Date field. The date in this table relates the staffMembers work hours to each particular day.

I figured that the relationship between the staffMember and timeSheetCalender would be one to one with a composite key of Date and StaffID.

I have edited the sql statement to this


Select
a.StaffId,
SUM(Datediff(hour,b.outtime, b.intime),
SUM(Datediff(hour,c.outtime, c.intime)
From StaffMember As a
Inner Join timeSheetCalender As b ON (a.StaffId=b.StaffId AND a.date=b.dateWorked)
Inner Join workHourTemplateTable As c ON c.workHourTemplateID=a.workHourTemplateID
group by a.staffid

Is this the correct syntax for dealing with this type of relationship?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:41:02

without sample data from tables its hard to suggest. can you post some sample data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources