WHERE fieldname IN (SELECT fieldname FROM #temptable)?
Posted on 2008-09-29
Please can someone help me here. I am sure this is quite obvious to most SQL people but I cannot work out what is going wrong!!!!
I have a T-SQL script that creates a temporary table...
CREATE TABLE #TempContainers
It then uses an INSERT command to fill it with multiple records.
Now, the following statement works as expected...
SELECT DRA_Container FROM #TempContainers
This gives me two records (PRDGB and OCDGB).
However, when I issue the following command...
SELECT * FROM DRA_Totals WHERE DRA_Container IN (SELECT DRA_Container FROM #TempContainers)
I only get the records from DRA_Totals with one of the DRA_Containers in #TempContainers and not the other one. Incidentally, if I don't use a SELECT in the brackets but specify the values (e.g. IN ('value1','value2')), it picks up both correctly.
Is this a problem with temporary tables or something else I have overlooked? Tried EXISTS too but don't think I fully understand how to get what I need :-)