CODEWITHSUNDEEP
sqlsql-servervariablesselect
Grayson
Grayson

Reputation: 21

SQL select from column where column equals table variable?

I'm a serious SQL noob so any help is appreciated. I'm having a hard time even explaining what I'm trying to do so I'll lay out what I have so far:

DECLARE @UserIDInt table (ID int);

INSERT into @UserIDInt
    SELECT UserId 
    FROM [LcsCDR].[dbo].[Users]
    WHERE [LcsCDR].[dbo].[Users].[UserUri] LIKE '%example';

SELECT * 
FROM [LcsCDR].[dbo].[SessionDetails]
WHERE [LcsCDR].[dbo].[SessionDetails].[User1Id] = @UserIDInt;


"DECLARE @UserIDInt table (ID int);"

This creates my variable with a column called "ID"

INSERT into @UserIDInt
    SELECT UserId 
    FROM [LcsCDR].[dbo].[Users]
    WHERE [LcsCDR].[dbo].[Users].[UserUri] LIKE '%example';

This adds numeric values into the ID column based on whether or not the WHERE statement matched

SELECT * 
FROM [LcsCDR].[dbo].[SessionDetails]
WHERE [LcsCDR].[dbo].[SessionDetails].[User1Id] = @UserIDInt;

This is where I am lost. I am trying to return all rows from [LcsCDR].[dbo].[SessionDetails] if the column [LcsCDR].[dbo].[SessionDetails].[User1Id] matches anything in my variable. The problem (I think) I'm having is that SQL can't look within the variable's column to find multiple values. Basically, the ID column in my variable @UserIDInt will contain a bunch of numeric values.

How do I perform the final SELECT statement and have SQL return all results if [LcsCDR].[dbo].[SessionDetails].[User1Id] matches anything within my @UserIDInt.ID column?

I am using SQL Server 2014.

Apologies if I explained it badly. Not sure how else to ask the question :)

Upvotes: 1

Views: 3037

Answers (2)

SqlZim
SqlZim

Reputation: 38043

using inner join:

select sd.*
from [lcscdr].[dbo].[sessiondetails] sd 
  inner join @useridint i
    on i.id = sd.user1id;

or using exists():

select sd.*
from [lcscdr].[dbo].[sessiondetails] sd 
where exists (
  select 1 
  from @useridint i
  where i.id = sd.user1id
  );

or using in():

select sd.*
from [lcscdr].[dbo].[sessiondetails] sd 
where sd.user1id in (
  select id
  from @useridint i
  );

rextester demo: http://rextester.com/UVCB28056

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Use EXISTS:

SELECT T1.*
FROM [lcscdr].[dbo].[sessiondetails] T1 WHERE EXISTS (
  SELECT 1 
  FROM @useridint T2
  WHERE T2.id = T1.user1id
  );

Upvotes: 0

Related Questions