How to store SQL query result into variable to include in a later SELECT statement

Question

I have this SQL query:

SELECT MAX(a.date), d.group, c.name, b.name_id 
FROM table1 a
LEFT JOIN table2 b ON a.id = b.id
LEFT JOIN table3 c ON b.name_id = c.name_id
LEFT JOIN table4 d ON b.group_id = d.group_id
WHERE a.foo = '12345'
GROUP BY d.group, c.name, b.name_id
ORDER BY MAX(a.date)

It returns something like this:

date	group	name	name_id
yyyy-mm-dd 00:00:00	FOOFOO	FA FA	1
yyyy-mm-dd 00:00:00	FOOFOO	FA FA	2
yyyy-mm-dd 00:00:00	FOOFOO	FA FA	3
yyyy-mm-dd 00:00:00	FOOFOO	FA FA	4
yyyy-mm-dd 00:00:00	FOOFOO	FA FA	5

Focusing on name_id, I want to take all of them (about 800) and store them into an array-like variable. Then utilize said array-like variable in the following type of clause in separate SELECT statement:

SELECT COUNT(a.id) 
FROM table999 a
WHERE a.foofoo IN @array

I've seen that you can build temporary tables that act as arrays, but I'm not sure if that's exactly what I'm looking for. I've also read that you can utilize variables (via @var) but it seems those can only hold one piece of data. Can what I'm looking for be done in SQL? Is there an array-like variable where I can push these name_id's to? Any help is super appreciated!

More likely, if I am honest, a JOIN or EXISTS could be more performant. Especially when you don't need all those JOINs from the original query to do that. — Thom A
– Thom A ♦, Commented Aug 22, 2022 at 17:59
JOIN will not work for this example. Already tried it and I received an incorrect count. The other JOIN's are necessary in this example. Regarding EXISTS how would I specify what name_id's I want in the subquery? If it's just use a WHERE clause with an IN statement, then I'd rather avoid all that work. — Crimp
– Crimp, Commented Aug 22, 2022 at 18:05

Thom A · Accepted Answer · 2022-08-22 18:36:43Z

2

I would suggest using an EXISTS here. You can also simplify your initial query as a result. You don't need table3 and table4 as these don't filter the data, and so would only slow the query down.

You just an INNER JOIN between tables table1 and table2. This can be changed to an INNER JOIN as you want t2.name_id to have a non-NULL value, which means that the JOIN must be true (though I do assume that name_id itself isn't NULLable).

This gives you the following query instead:

SELECT COUNT(a.id)
FROM dbo.table999 t999
WHERE EXISTS(SELECT 1
             FROM dbo.table2 t2
                  JOIN dbo.table1 t1 ON t2.id = t1.id
             WHERE t1.foo = '12345'
               AND t2.name_id = t999.foofoo);

There's little need to a table variable here (what you call an "array", which SQL Server doesn't have), as this would just be an additional cost to run the first query (with all its additional JOINs), store that data somewhere (which might be in RAM, but could be in a table in tempdb), and then read that data back.

edited Aug 22, 2022 at 18:36

answered Aug 22, 2022 at 18:07

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

3 Comments

Crimp Over a year ago

What is t999.foofoo refer to? I understand that t999 refers to table 999 but what is .foofoo referring to?

Thom A Over a year ago

The column foofoo, @Crimp . That's the name you used, so it's the name I used.

Crimp Over a year ago

Lol. Just now saw that. My apologies

Matt Bommicino · Accepted Answer · 2022-08-22 18:07:18Z

2

use a table variable, it's different from a temporary table because it only exists in memory for the length of the batch. Note: this is only good for SQL Server.

declare @mytablevar TABLE (name_id int);


INSERT INTO @mytablevar
SELECT b.name_id FROM table1 a
LEFT JOIN table2 b ON a.id = b.id
LEFT JOIN table3 c ON b.name_id = c.name_id
LEFT JOIN table4 d ON b.group_id = d.group_id
WHERE a.foo = '12345'
GROUP BY d.group, c.name, b.name_id
ORDER BY MAX(a.date);


SELECT COUNT(a.id) FROM table999
WHERE a.foofoo IN (SELECT name_id FROM @mytablevar);

```

edited Aug 22, 2022 at 18:07

answered Aug 22, 2022 at 18:05

Matt Bommicino

3491 silver badge5 bronze badges

Comments

Patrick · Accepted Answer · 2022-08-22 18:51:14Z

0

You can use a common table expression and a subquery in your IN clause.

WITH tempTable(foofoo) AS (select foofoo from firsttable)  
SELECT COUNT(table999.id) FROM table999
WHERE table999.foofoo IN (select foofoo from tempTable)

edited Aug 22, 2022 at 18:51

answered Aug 22, 2022 at 18:00

Patrick

1697 bronze badges

2 Comments

Sean Lange Over a year ago

This looks like it is likely a valid approach but it is not a temporary table. This is using a common table expression (cte).

Patrick Over a year ago

you are correct.

Collectives™ on Stack Overflow

How to store SQL query result into variable to include in a later SELECT statement

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related