CODEWITHSUNDEEP
sql-server
Jonathan Kittell
Jonathan Kittell

Reputation: 7503

Sql Join to get values in two lists

Microsoft SQL Server 2012

I have three scalar variables:

declare @ListA table (value int);
declare @ListB table (value int);
declare @InBoth table (value int);

I want to get a list of numbers that are in both lists.

insert into @InBoth 
   select value 
   from @ListA 
   inner join @ListB on @ListA.value = @ListB.value;

I get an error

Must declare the scalar variable

for both @ListA and @ListB inside the join statement.

How do I get a list of numbers that are in both lists?

Upvotes: 3

Views: 741

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

You cannot use table variable name as alias instead give alias name to the table variable and use the alias name to identify the columns

INSERT INTO @InBoth
SELECT A.value
FROM   @ListA A
       INNER JOIN @ListB B
               ON A.value = B.value;

or use EXISTS operator

INSERT INTO @InBoth
SELECT A.value
FROM   @ListA A
WHERE  EXISTS (SELECT 1
               FROM   @ListB B
               WHERE  A.value = B.value)

Upvotes: 4

Related Questions