Sub Query Problem

[greg52]

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.

 

[gmmastros]

What have you tried so far? Can you show us your query?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

SELECT TableB.*,
       TableA.SSN
FROM TableB
INNER JOIN TableA ON TableB.EmployId = TableA.EmployId
LEFT JOIN (SELECT EmployId
                  FROM TableB
                  WHERE Paycode = '401') Tbl1
ON TableB.EmployId = Tbl1.EmployId
WHERE Tbl1.EmployId IS NOT NULL

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[greg52]

Thanks, Borislav. It did the trick.

Greg