#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9

    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!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    change INNER JOIN to LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    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!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    Thanks again! I picked up your book in hopes of limiting my questions in the future!

  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    dude, thanks !!!

    the paper version is actually a collector's item, sitepoint only sells the ebook now...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo