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

Question

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.

Thanks in advance for your help!

Answer 1

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.

Share and enjoy.

Answer 2

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

Have you tried it without the t. in the subquery? as looking at your original subquery your not giving the table tests an alias of t...

Answer 3

I've never tried a subquery in an IN clause, so I can't guarantee this works, but try this:

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

I simply added parentheses around your subquery, which is common practice. Again, I've never tried a subq in an IN clause so can't guarantee this works but it's worth a shot.

Answer 4

First off, you are not aliasing tests properly. Should be:

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

Secondly, if t.person_id returns any NULLs you will not get any results.