JOIN Help

Transact-SQL
1

Hi All,

I need some basic help with a SQL query to join 3 tables together. I have three tables that have a common field across them and when joining two together I get the expected results, however when trying to join the 3 tables the query times out.

Table A has the following fields

  • ID
  • UserID
  • Name
  • Branch

Table B has the following fields

  • ID
  • UserID
  • Date
  • Workday
  • In
  • Out

Table C has the following fields

  • ID
  • UserID
  • Checktime

Table C has multiple entries for each UserID, when a user signs in at our wall clock an entry is put created here, this can multiple times a day (simply by the user double scanning or not sure if they did so they scan again - however we are only taking the first entry for IN and the last entry as OUT.

From here the data is moved into Table B and each entry is put into the columns, The issue we have is that Table B isn't getting filled out properly even though there is a valid record in table C.

I want to join the three tables together to run a little report to show me the following output
A.Name, A.Branch, B.Date, B.In, B.Out, C.Checktime

I'm hoping it will provide me an easy report to identify the records that aren't coming across from table C to table B.

The query I have thus far is:

select A.Name, A.Branch, B.Date, B.In, B.Out, C.Checktime from Table A
JOIN Table B ON A.UserID = B.UserID
LEFT JOIN Table C ON U.UserID = W.UserID
where A.DayType = 'Workday' and A.Date >= '2018-06-01'

So I'm expecting something like
John Smith, Head Office, 2018-06-01, NULL, NULL, 2018-06-01 10:00
Foo Bar, Singapore, 2018-06-01, 2018-06-01 8:27, NULL, 2018-06-01 8:27
Foo Bar, Singapore, 2018-06-01, 2018-06-01 8:27, NULL, 2018-06-01 17:00

From here, I can tell that John Smith had signed in but the record didn't get brought across to table B, but I can see that Foo Bar signed in and the record from table C flowed through table B for the sign IN but Foo Bar sign out at 5pm didn't flow through to table B.

I appreciate your time and help people.

Thanks Jason

1 Like
2

We only want a subset of rows from table C (a min and max checktime) so that hints at an aggregation and we want to include rows if there is a corresponding missing row in B, so that's going to be a left join (and we want to avoid using columns from B in our Where clause or we'll sneakily turn in back into an effective Inner Join).

So it'll probably look something like:

select A.Name, A.Branch, B.Date, B.In, B.Out, C.CheckIn, C.CheckOut
from TableA A
cross apply
(
  Select 
    min(CheckTime) as CheckIn,
    max(CheckTime) as CheckOut
  From TableC
  Where Cast(C.CheckTime As Date) = '2018-06-01'
      And UserID = A.UserID
) C
left join TableB B on A.UserID = B.UserID
   And B.Date = Cast(C.CheckIn As Date)
Where A.DayType = 'Workday'

Which gives a single row variant similar to what you were going for. If you need two different rows or if odd things can happen (working past midnight for example), you probably just need to slightly rework it to get the IN times as one set, the OUT times as another (because they'll need different JOIN conditions) and then UNION the two together.

Depending on the volume of data, you might also need to consider how your tables are structured - Do they all have an appropriate Clustered Index? Are the foreign keys indexed? And so on - as that will also have implications once the dataset gets above a moderately small size.

1 Like