How to select items from table by using a @local_variable table

Question

Currently in a simple form i have the following declared table in code:

declare @FileIDs as table
(
    ID int not null
)

and i can fill it up e.g. manually like this:

insert into
    @FileIDs
values
    (1)

insert into
    @FileIDs
values
    (2)

insert into
    @FileIDs
values
    (3)

Also i have another table called Files and i like to select only those items, that have the same ID.

So I've tried the following approaches, but both fail:

select
    *
from
    Files
where
    ID in
    (
        @FileIDs
    )

select
    *
from
    Files
    inner join @FileIDs
    on Files.ID = @FileIDs.ID

Any ideas on how i can solve this problem?

Answer 1

You can do either

select 
    * 
from 
    Files 
where 
    ID in 
    ( 
        SELECT ID FROM @FileIDs 
    ) 



select 
    f.* 
from 
    Files f
    inner join @FileIDs fid
    on f.ID = fid.ID 

In the first query your syntax was incorrect. You had to have

SELECT ID FROM @FileIDs

And in the second you need to assign an alias to the table variable.

Answer 2

do it like this

declare @FileIDs as table
(
    ID int not null
)
insert into
    @FileIDs
values
    (1)

insert into
    @FileIDs
values
    (2)

insert into
    @FileIDs
values
    (3)

;


select
    *
from
    Files
    inner join (select * from @FileIDs) F
    on Files.ID = F.ID

Answer 3

This will work:

select * from Files 
where id in (select ID from @FileIDs)

The first attempt you made needed to have a column name in the subquery.