CODEWITHSUNDEEP
sql-servertemp-tables
Aaron C
Aaron C

Reputation: 135

SQL: use ID's in temp table to query another table

I have a query which loads data into a temporary table

SELECT * INTO #tempTable FROM Players where [reference month] = '2016-08-01'

I now need to use these ID's to find them in the previous month

SELECT ID FROM Players WHERE [reference month] = '2016-07-01' AND EXISTS 
(SELECT #tempTable.ID FROM #tempTable)

I have tested the #tempTable (SELECT * FROM #tempTable) which returns 346 records which is correct.

The above code is searching every record (1000+) in the Players table and not the specific ID's that are found in the #tempTable

How can I fix this query to only use the ID's found in the #tempTable?

Upvotes: 2

Views: 3014

Answers (3)

user7062779
user7062779

Reputation: 51

You may try any of the following options,

SELECT ID
FROM Players
WHERE [reference month] = '2016-07-01'
    AND EXISTS (
        SELECT T.ID
        FROM #tempTable T
        WHERE T.ID = Players.ID
    )

OR

SELECT P.ID
FROM Players P
INNER JOIN #tempTable T
    ON  T.ID = Players.ID
WHERE P.[reference month] = '2016-07-01'

Upvotes: 3

neer
neer

Reputation: 4092

You are almost close. Just missing WHERE clause in EXISTS

SELECT ID FROM Players WHERE [reference month] = '2016-07-01' AND EXISTS 
(SELECT T.ID FROM #tempTable T WHERE T.ID = Players.ID)

Upvotes: 1

Ponnarasu
Ponnarasu

Reputation: 635

Try this 

SELECT ID
FROM Players
WHERE [reference month] = '2016-07-01'
    AND ID IN (
        SELECT #tempTable.ID
        FROM #tempTable
    );

Upvotes: 1

Related Questions