simple query.. I think - 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)

simple query.. I think

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-07 : 10:39:12

Hi, I have an issue with a query.
I have 2 tables, 1 for employee, 1 for training record.

I wish to show all employee and if they have had training for a course. p.s. I also want to show if the were not on a course.

ie, full employee list with details if they have been on the course yet.

Employee table contains - ref, name
Training Record Contains, - ref, course ref, event ref, start time, status

I cant for the life of me write a query to show all the info I need.
?
thanks
Craig.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 13:09:32

use left outer join .. hope you will get what you want ...

By the way always post some sample data and desired output format ..so that we can come up with a confirm response. At the moment I just guessed about your requirement and if its so then the left outer join will work for you

Cheers!
MIK

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 03:52:47

left out would work, but the problem is that I want to specify a course ref from the 2nd table but also want to show employee's who have not been on the training course.

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 04:02:41

Sample Data:
Employee Table

Employee Ref,Employee Name
100, Joe Bloggs
101, A User
102, John Doe

Training Record Table
Employee Ref,Course Ref, Event Ref, Start Time, Status
100,3,196,1/6,2010,ABSENT
101, 1,198,1/1/2011, COMPLETED
101, 2, 197,1/1/2010, ABSENT
101, 3, 196,1/6/2010, COMPLETED
102, 2, 197,1/1/2010, COMPLETED

Desired output:

Employee Ref, Employee Name,Course Ref, Status
100, Joe Bloggs, Null, Null
101, A User,1, COMPLETED
102, John Doe,Null, Null

N.B. I am only interested in outputting data based on course Ref 1.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 04:34:24

You can specify the columns of another Table(training Record) as well. However with left join it will display null under the columns of training record table when no matching record exists

, whereas for the rest it would return their values.... the following will give you desired output

SELECT
E.EmployeeRef,E.EmployeeName,TR.CourseRef,TR.Status
From Employee E
Left Join TrainingRecord TR on E.employeeRef=TR.employeeRef

Cheers!
MIK

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 04:46:39

this will return all training record info for all course refs, I'm only interested in a single course ref of 1.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 05:16:34

obviously without any condition it will give you all what you have in tables

Therefore use the Where clause at the bottom in order to filter the data set .. e.g.

SELECT
E.EmployeeRef,E.EmployeeName,TR.CourseRef,TR.Status
From Employee E
Left Join TrainingRecord TR on E.employeeRef=TR.employeeRef
Where tr.courseRef=.....?

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 05:25:07

I know this, but what you dont understand is that I want to show the status of THIS course for ALL employee's in the employee table.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 05:43:39

ok my mistake

Check this out !

SELECT
E.EmployeeRef,E.EmployeeName,TR.CourseRef,TR.Status
From Employee E
Left Join TrainingRecord TR on E.employeeRef=TR.employeeRef
And tr.courseRef=1

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 05:57:37

doesnt work.. it only returns rows that are present in the Training Record table, ie in this case 1 row.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 06:05:02

quote:
Originally posted by cjonline

Sample Data:
Employee Table

Employee Ref,Employee Name
100, Joe Bloggs
101, A User
102, John Doe

Training Record Table
Employee Ref,Course Ref, Event Ref, Start Time, Status
100,3,196,1/6,2010,ABSENT
101, 1,198,1/1/2011, COMPLETED
101, 2, 197,1/1/2010, ABSENT
101, 3, 196,1/6/2010, COMPLETED
102, 2, 197,1/1/2010, COMPLETED

Desired output:

Employee Ref, Employee Name,Course Ref, Status
100, Joe Bloggs, Null, Null
101, A User,1, COMPLETED
102, John Doe,Null, Null

N.B. I am only interested in outputting data based on course Ref 1.

what do you mean ..does it not give you what you asked in your example .. i checked it with same data. Copy/paste the below code and check yourselves

Declare @tab1 table (employeeRef int,employeeName varchar(100))
Insert into @tab1 VALUES (100, 'Joe Bloggs'),(101, 'A User'),(102, 'John Doe')
Declare @tab2 table (EmployeeRef int,CourseRef int, EventRef int, StartTime datetime, [Status] varchar(100))
Insert into @tab2 values (100,3,196,'1/6/2010','ABSENT'),
(101, 1,198,'1/1/2011','COMPLETED'),
(101, 2, 197,'1/1/2010', 'ABSENT'),
(101, 3, 196,'1/6/2010', 'COMPLETED'),
(102, 2, 197,'1/1/2010', 'COMPLETED')

SELECT
E.EmployeeRef,E.EmployeeName,TR.CourseRef,TR.Status
From @tab1 E
Left Join @tab2 TR on E.employeeRef=TR.employeeRef
And tr.courseRef=1

Isn't the output of this select is same as the above highlighted one?

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 06:52:05

you are right enough.. your sample works.

but when I use the following

select e.employ_ref,e.surname
from employee e
left join trainrec tr on e.employ_ref = tr.employ_Ref
where e.leaver=0
and tr.course_ref='1'

it returns 567 rows.
although, when I use the following:

select e.employ_ref,e.surname
from employee e
where e.leaver=0

it returns 1154 rows (The correct amount)

??

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 07:04:48

use this one instead the one you're using

select e.employ_ref,e.surname
from employee e
left join trainrec tr on e.employ_ref = tr.employ_Ref
And e.leaver=0
and tr.course_ref='1'

And let me know if the result is ok now

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 07:27:57

5538 rows returned!

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 07:34:15

I got it.. thanks, just needed to take the e.leaver=0 and move it to where e.leaver=0.

thanks for your Help.
craig.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 07:38:23

sorry i don't know what are you trying to achieve ..... as i showed you how you can achieve what you want as per given example...

Any how ..get this last try and still if this does not work then i think we have to rest for a day

select e.employ_ref,e.surname
from employee e
left join trainrec tr on e.employ_ref = tr.employ_Ref
and tr.course_ref='1'
Where e.leaver=0

P.S. come on query .. give cj 1154 rows ...

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-02-08 : 08:00:15

thanks again!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 08:01:12

yrw :)

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources