MSSQL - Trouble Combining Tables

Dev Shed Forums Sponsor:

September 10th, 2012, 04:42 PM

gmrstudios

Contributing User

Join Date: Jun 2008

Posts: 119

Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7

MSSQL - Trouble Combining Tables

Hello and thank you for reading. I am running into trouble when I try to combine two tables. The only relationship the two tables have a "badge_no" column. The "employee" table contains the employees information while the "punch_data" table contains the individual clock-in/outs of the employee. The goal is to show all active employees and whether or not they are clocked in or out. Problem is only hourly employees exist in the "punch_data" table.

I can do the following query to pull up all active employees:

PHP Code:


		
			
 SELECT

     badge_no

    ,emp_lname

    ,emp_fname

    ,dept_id

    ,ss_number

    ,status

FROM employee

WHERE

    (status = 'A')

When I do an INNER JOIN on the "punch_data" table using the only relationship the two tables have "badge_no".

PHP Code:


		
			
 SELECT

    employee.badge_no,

    employee.emp_lname,

    employee.emp_fname,

    employee.dept_id,

    employee.ss_number,

    employee.status, 

             punch_data.punch_timestamp, 

    punch_data.source, 

    punch_data.in_out

FROM employee 

INNER 

    JOIN

         punch_data ON punch_data.badge_no = employee.badge_no

WHERE     

    (employee.status = 'A')

I get the data I want minus the non-hourly employees which makes complete sense as they would have never clocked in.

Is there a way to pull a record of all active employees and display the last recorded punch_timestamp, source, and in_out IF they had one?

Thank you for reading!

September 10th, 2012, 07:36 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 33 m 44 sec
Reputation Power: 4140

change INNER JOIN to LEFT OUTER JOIN

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

September 11th, 2012, 12:34 PM

gmrstudios

Contributing User

Join Date: Jun 2008

Posts: 119

Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7

Thank you! This is being used to gather the current location of an employee. If I wanted to pull up all current employees this works just fine. However, the use of the OR statement makes any search time (will use PHP eventually) I pass to the query useless. I tried doing a GROUP BY but it failed. I am trying to limit the results to only show the record of the last punchtime of the employee while at the same time still displaying those with a NULL value.

For example:

Code:

First   Last    Source  	Punch Timestamp
BINDA	LINDA	10.1.2.144	NULL
SMIAN	DIANE	NULL		NULL
SIRCH	CHRIS	NULL		NULL
SAMA	SAM	10.1.2.198	2/3/2012   5:06:44 PM
SAMA	SAM	10.1.2.220	12/27/2011 3:06:44 PM
SAMA	SAM	10.1.2.220	12/27/2011 3:08:33 PM
AMBRO	TARVS	NULL		NULL	
SHMEC	BECKY	NULL		NULL
OGOLE	TAMI	10.3.1.114	NULL

Should display as:

First   Last    Source  	Punch Timestamp
BINDA	LINDA	10.1.2.144	NULL
SMIAN	DIANE	NULL		NULL
SIRCH	CHRIS	NULL		NULL
SAMA	SAM	10.1.2.198	2/3/2012   5:06:44 PM
AMBRO	TARVS	NULL		NULL	
SHMEC	BECKY	NULL		NULL
OGOLE	TAMI	10.3.1.114	NULL

September 11th, 2012, 03:46 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 33 m 44 sec
Reputation Power: 4140

Code:

SELECT employee.badge_no
     , employee.emp_lname
     , employee.emp_fname
     , employee.dept_id
     , employee.ss_number
     , employee.status
     , punch_data.punch_timestamp
     , punch_data.source
     , punch_data.in_out
  FROM employee
LEFT OUTER
  JOIN ( SELECT badge_no
              , MAX(punch_timestamp) AS latest_punch
           FROM punch_data
         GROUP
             BY badge_no ) AS latest
    ON latest.badge_no = employee.badge_no
LEFT OUTER
  JOIN punch_data
    ON punch_data.badge_no = latest.badge_no
   AND punch_data.punch_timestamp = latest.latest_punch
 WHERE employee.status = 'A'

September 11th, 2012, 06:47 PM

gmrstudios

Contributing User

Join Date: Jun 2008

Posts: 119

Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7

So can put a SELECT Statement within a JOIN and then assign the results to a variable? I think you then used it later to compare it to timestamp?

I just want to make sure I some what understand what was done! Thanks!

September 11th, 2012, 06:54 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 33 m 44 sec
Reputation Power: 4140

Quote:

Originally Posted by gmrstudios

So can put a SELECT Statement within a JOIN and then assign the results to a variable?

not a variable per se

a subquery in the FROM clause is usually called a derived table

it works kind of like this -- the subquery runs, produces a table, you give that table a name, and then you can join to it and reference its columns

so the columns of this derived table are badge_no and latest_punch, and you can use these rows (one row per badge_no) to pick the corresponding rows of the punch_data table which match the latest_punch value