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:

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!

CodePudding user response：

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.

CodePudding user response：

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);

```

CodePudding user response：

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)