CODEWITHSUNDEEP
sql
binjamin
binjamin

Reputation: 123

Using table information inside a WHERE clause

So some background for this question. I have created a query that looks checks SCCM for every piece of software given single machine. So I have my statement: 

SELECT Programs.DisplayName, Programs.Publisher etc..
FROM my_db
WHERE machine_Name = @computername

Above is just an example of what I do. The point is that this query returns a list of software that is in SCCM for that single machine.

Now my issue is that I have been given a massive list of machines to find software for.

What I want to do is to somehow make a single query that will take in a list of machines and return results for every name in that list (which is like 300 lines).

I am trying to see if it is possible to make a table with the list of machine names and use that table in my WHERE clause. Is this something that cannot be done in SQL alone or is this possible?

I know you can use comma delimited arrays in WHERE clauses but there are so many names that I am not sure that will work.

Upvotes: 0

Views: 30

Answers (1)

GMB
GMB

Reputation: 222432

Storing the list of machines in a separate table is a good idea. While it is doable to build a long list of values that you can use on the right side of the IN operator, using a reference table is more scalable (lists are limited to a few thousands elements), and easier to maintain.

Once you have this table created and filled, you can just join it with your table. Assuming that your reference table is called machines and has a column named machine, this would look like:

SELECT p.*
FROM programs p
INNER JOIN machines m ON m.machine_name = p.machine_name

Upvotes: 1

Related Questions