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

Question

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?

INNER JOIN or use IN rather than EXISTS

Mr Moose
– Mr Moose

2016-10-25 08:15:56 +00:00
Commented Oct 25, 2016 at 8:15 — Mr Moose
– Mr Moose, Commented Oct 25, 2016 at 8:15

Edward Comeau · Accepted Answer · 2016-10-25 08:57:38Z

3

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'

edited Oct 25, 2016 at 8:57

Edward Comeau

4,1742 gold badges24 silver badges24 bronze badges

answered Oct 25, 2016 at 8:26

user7062779

513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron C Over a year ago

I went with the first option - and it worked - thanks

Edward Comeau · Accepted Answer · 2016-10-25 08:25:00Z

1

Try this

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

edited Oct 25, 2016 at 8:25

Edward Comeau

4,1742 gold badges24 silver badges24 bronze badges

answered Oct 25, 2016 at 8:19

Ponnarasu

6351 gold badge12 silver badges26 bronze badges

Comments

neer · Accepted Answer · 2016-10-25 08:25:01Z

1

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)

answered Oct 25, 2016 at 8:25

neer

4,0926 gold badges22 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related