join two tables
-
For some reason I am just going about this the wrong way.
There are 3 tables -
employees : id, employee
(1, 'Jack')(2,'Jill')
codes : id, code, description, required
(1, 123, 'test', 1) (2, 321, 'these',0)(3,456,'codes',0)(4,654,'now',0)
hours : id, employee_id, code_id, hours
(1,1,1,5)(2,1,2,3)(3,2,4,8)
What I am trying to do is return a set of results that has all of the required rows, and any of the optional ones that may be assigned. Er, an example may help.
Jack - has 2 required, no optional - return
Jack, 123, 'test', 1, 5
Jack, 321, 'these', 1, 3
Jill - no required, 1 optional - return
Jill, 123, 'test', 1, 0
Jill, 321, 'these', 1, 0
Jill, 654, 'now', 0, 8
I was trying:
select a.employee, b.code, b.description, b.required, c.hours
from
time_hours as c
inner join time_employee as a on a.id = c.employee_id
left join time_code as b on b.id = c.code_id or b.required = 1
where a.id = 2
but this would give me
Jill, 123, 'test', 1, 8
Jill, 321, 'these', 1, 8
Jill, 654, 'now', 0, 8
Jack, 123, 'test', 1, 5
Jack, 123, 'test', 1, 3
Jack, 321, 'these', 1, 3
Jack, 321, 'these', 1, 5
which is not what I am looking for. Any suggestions on how to add 0 hours for the rows that don't join up nicely?
-
It's not clar what you are trying to do. Why do you want to return
Jill, 123, 'test' , 1, 8
Jill, 321, 'these', 1, 8
? What is the logic of required v optional? One guess is that if a person has any optional item assigned, they should also be assigned all the other optional items. If this is so, there is some redesign work to do on the database.
From your initial description of the problem, I thought you just needed to remove
or b.required = 1
from the join predicate, i.e. treat the 'required' flag as just another data field without any special significance for the query structure. But the example above suggests that you are looking for something elsea. Exactly what are your requirements?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I don't think that I am 100% clear on what you are after here, but here's what I have so-far. The below table defs include the data from your post along with my interpretation of your DDL for the three tables. The part that I am unclear on is the result set that you are after. I have also included a SELECT statement that properly JOINS the three tables. If this is not the data you are after, can you list out exactly how you want your data to look in the result?
create table employees (
id int,
employee varchar(25)
)
insert into employees
select 1, 'Jack' union all
select 2,'Jill'
create table codes (
id int,
code int,
description varchar(25),
required bit
)
insert into codes
select 1, 123, 'test', 1 union all
select 2, 321, 'these',0 union all
select 3,456,'codes',0 union all
select 4,654,'now',0
create table hours (
id int,
employee_id int,
code_id int,
hours int
)
insert into hours
select 1,1,1,5 union all
select 2,1,2,3 union all
select 3,2,4,8
select E.employee,
C.code,
C.description,
C.required,
H.hours
from Hours H
INNER JOIN Employees E
ON E.ID = H.Employee_ID
INNER JOIN Codes C
ON C.ID = H.code_id
-
[OMG! I did not realize so many of us got the same thing... too funny] I do not get your results:
DECLARE @employees TABLE( [id] integer, employee varchar(10))
INSERT INTO @employees SELECT 1, 'Jack'
INSERT INTO @employees SELECT 2,'Jill'
DECLARE @codes TABLE( [id] integer, code integer, [description] varchar(10), required integer)
INSERT INTO @codes SELECT 1, 123, 'test', 1
INSERT INTO @codes SELECT 2, 321, 'these',0
INSERT INTO @codes SELECT 3,456,'codes',0
INSERT INTO @codes SELECT 4,654,'now',0
DECLARE @hours TABLE( [id] integer, employee_id integer, code_id integer, hours integer)
INSERT INTO @hours SELECT 1,1,1,5
INSERT INTO @hours SELECT 2,1,2,3
INSERT INTO @hours SELECT 3,2,4,8
SELECT a.employee, b.code, b.[description], b.required, c.hours
FROM @hours c
INNER JOIN @employees a ON( a.[id] = c.employee_id)
LEFT JOIN @codes b ON( b.[id] = c.code_id OR b.required = 1)
WHERE a.[id] = 2
-------------- results -----------------------------------------------------------------
employee code description required hours
---------- ----------- ----------- ----------- -----------
Jill 123 test 1 8
Jill 654 now 0 8
I wasn't born stupid - I had to study.
-
-- Prepare test data
declare @employees table (id tinyint, employee char(4))
insert @employees
select 1, 'Jack' union all
select 2, 'Jill'
declare @codes table (id tinyint, code smallint, description varchar(5), required bit)
insert @codes
select 1, 123, 'test', 1 union all
select 2, 321, 'these', 1 union all -- Must be changed to comply with original expected output
select 3, 456, 'codes', 0 union all
select 4, 654, 'now', 0
declare @hours table (id tinyint, employee_id tinyint, code_id tinyint, hours tinyint)
insert @hours
select 1, 1, 1, 5 union all
select 2, 1, 2, 3 union all
select 3, 2, 4, 8
-- Do the work
SELECT e.Employee,
c.Code,
c.Description,
c.Required,
ISNULL(h.Hours, 0) Hours
FROM @Employees e
CROSS JOIN @Codes c
LEFT JOIN @Hours h ON h.Code_ID = c.ID AND h.Employee_ID = e.ID
WHERE c.Required = 1
UNION
SELECT e.Employee,
c.Code,
c.Description,
c.Required,
h.Hours
FROM @Codes c
INNER JOIN @Hours h ON h.Code_ID = c.ID
INNER JOIN @Employees e ON e.ID = h.Employee_ID
WHERE c.Required = 0
ORDER BY e.Employee,
c.Code
Output is
Employee Code Description Required Hours
-------- ---- ----------- -------- -----
Jack 123 test 1 5
Jack 321 these 1 3
Jill 123 test 1 0
Jill 321 these 1 0
Jill 654 now 0 8
N 56°04'39.16"
E 12°55'05.25" -
Here is a shorter version
-- Do the work
SELECT e.Employee,
c.Code,
c.Description,
c.Required,
ISNULL(h.Hours, 0) Hours
FROM @Employees e
CROSS JOIN @Codes c
LEFT JOIN @Hours h ON h.Code_ID = c.ID AND h.Employee_ID = e.ID
WHERE c.Required = 1 OR c.Required = 0 AND h.Hours IS NOT NULL
ORDER BY e.Employee,
c.Code
Output is
Employee Code Description Required Hours
-------- ---- ----------- -------- -----
Jack 123 test 1 5
Jack 321 these 1 3
Jill 123 test 1 0
Jill 321 these 1 0
Jill 654 now 0 8
N 56°04'39.16"
E 12°55'05.25" -
Sounds like homework to me.
-
Thanks all for the replies, I'm still reading though a few of them.
Ray - I wish this were homework, at least then I could ask a prof a question
I'm actually working on a timecard application and used simplified tables for the examples here so people wouldn't have to look at a bunch of extraneous info.
Since my intent wasn't clear to everyone I will try and explain it better this time around for those still interested (though I bet the answer I'm looking for is in the replies already posted).
For this application, a user is supposed to log in and be able to enter their hours for the week. But there will end up being hundreds of optional cost codes and about a dozen required ones. To make it easier on the user all of the required rows will show up plus any of the optional ones they used last pay period. They will be able to add new rows of optional cost codes as needed, but shouldn't see all of the optional ones as that defeats the purpose of optional and required.
This is why I needed to get rows that have 0 hours in them. When I present the required rows that they haven't used before, there shouldn't be anything in them. Also, a user can go back and edit the timecard part way through the week so I needed to actually grab the hours that are there.
Fun stuff! Anyways I'll get back to reading the responses and let you all know how it goes.
Bob
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply