[SOLVED] SQL subquery matches hard-coded IN criteria, but not subquery

SQL subquery matches hard-coded IN criteria, but not subquery

I have a group of people who have taken a test. I can select their IDs with this query:

SELECT person_id
FROM tests
WHERE test_code = 1234

I'd like to pull these individuals' records from a demographics table, so I tried this subquery to do so:

SELECT *
FROM demographics d
WHERE d.person_id IN (
    SELECT t.person_id
    FROM tests t
    WHERE t.test_code = 1234
)

... but I'm not getting any results. If I take a couple of the IDs from the (functional) subquery and hard-code them into the IN criteria:

SELECT *
FROM demographics d
WHERE d.person_id IN (01123, 58132)

... the query works. I've got to be missing something really fundamental here - do you know what it is?

Possible complicating factors: t.person_id is char13, d.person_id is varchar50. This is MS SQL Server 9.0.4035; I'm working in SQL Server Management Studio.

Solution

The problem may be that TESTS.PERSON_ID is coming in with trailing blanks on it since it's declared as CHAR instead of VARCHAR. I'm not sure how to remove trailing blanks in SQL Server (it's been a while since I used it), but in Oracle I'd use the TRUNC function, as in

SELECT * 
  FROM demographics d 
  WHERE d.person_id IN ( 
    SELECT TRUNC(t.person_id )
      FROM tests t 
      WHERE t.test_code = 1234 )

Edit: I believe the equivalent function in SQL Server is RTRIM.