I have a MS SQL table structure that looks like the following:
Table a:
Employid SSN
12345 123456789
23456 456789123
34567 789123456
Table b:
Employid Chekdate Paytype Paycode Hours Amt
12345 2006-05-01 1 Hourly 40 900
12345 2006-05-01 2 401K 0 50
12345 2006-05-08 1 Hourly 40 900
12345 2006-05-08 2 401K 0 50
23456 2006-05-01 1 Hourly 40 700
34567 2006-05-01 1 Hourly 40 1000
34567 2006-05-01 2 401K 0 60
I would like to pull all Hourly and 401K rows for any employee who had 401K deductions last year. The desired output would look like this:
Employid SSN Chekdate Paycode Hours Amt
12345 123456789 2006-05-01 Hourly 40 900
12345 123456789 2006-05-01 401K 0 50
12345 123456789 2006-05-08 Hourly 40 900
12345 123456789 2006-05-08 401K 0 50
34567 789123456 2006-05-01 Hourly 40 1000
34567 789123456 2006-05-01 401K 0 60
I do not have any problems joining the two tables. However, no matter what I try using a join or sub query, I get either the 401K rows only, the Hourly rows only, or Hourly rows for employees that did not have 401K deductions mixed in with the desired output.
Can anyone point me in the right direction? Thanks.
Table a:
Employid SSN
12345 123456789
23456 456789123
34567 789123456
Table b:
Employid Chekdate Paytype Paycode Hours Amt
12345 2006-05-01 1 Hourly 40 900
12345 2006-05-01 2 401K 0 50
12345 2006-05-08 1 Hourly 40 900
12345 2006-05-08 2 401K 0 50
23456 2006-05-01 1 Hourly 40 700
34567 2006-05-01 1 Hourly 40 1000
34567 2006-05-01 2 401K 0 60
I would like to pull all Hourly and 401K rows for any employee who had 401K deductions last year. The desired output would look like this:
Employid SSN Chekdate Paycode Hours Amt
12345 123456789 2006-05-01 Hourly 40 900
12345 123456789 2006-05-01 401K 0 50
12345 123456789 2006-05-08 Hourly 40 900
12345 123456789 2006-05-08 401K 0 50
34567 789123456 2006-05-01 Hourly 40 1000
34567 789123456 2006-05-01 401K 0 60
I do not have any problems joining the two tables. However, no matter what I try using a join or sub query, I get either the 401K rows only, the Hourly rows only, or Hourly rows for employees that did not have 401K deductions mixed in with the desired output.
Can anyone point me in the right direction? Thanks.