Use Result of one SQL Query in Another SQL Query
How can I have the result of one query be a parameter in a second query. The screenshot shows what I am attempting to do. In the first I get the software id of a particular piece of software and then that software id is used to generate a list of computers that have it installed. The software versions get updated fairly regularly and this would automate the list, without hardcoding in something that has to be changed regularly.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
6 years ago
You use a sub select statement:
SELECT column1, column2, column3 from table
WHERE column4 = (select column5 from another table)
That's a very simple example, obviously. The sub select statement needs to return just one value in this instance, so the query should just return the SC.ID. If you need the outer statement to match multiple values, then you need to craft the sub select statement to return a comma separated list using GROUP_CONCAT and group the results. Your where statement would then become column4 in (select group_concat(column5) from table group by column6).
Posted by:
c.castellari
6 years ago
You may use smart sql labels:
1. Create a Device Smart Label and name it (ie: "PCs with Solstice"). Save it. Go again in editing mode and click Edit SQL. Paste your first SQL code and save it
2. Create a report and apply it only to the "PCs with Solstice" label
Comments:
-
The first sql query tells me the software_id (from the software catalog) of solstice. Then that software_id is used to generate the list of machines with the second query. I am not following your example. Can you elaborate? The results of the first query doesn't return a device. It returns an id. I went ahead and tried your steps, just to see. I don't see a place when creating a report to apply it to a label. - five. 6 years ago
-
Sorry, my mistake.
Try this:
1. Home -> Label Management -> Smat Label
2. Create a new *Software* Smart Label
3. In the "Choose label" box, set a name for your Smart Label (ie: Solstice)
4. Save the Smart Label
5. Go again in Home -> Label Management -> Smat Labels and click the name of your new Smart Label
6. Click Edit SQL and paste your SQL
Now you must wait that some devices complete their inventory, or force the inventory for some PCs, or wait tomorrow (the smart label mechanism updates its datas at 4 AM)
Now your *software* smart label must contain at least one row.
Create your report and filter it only for the smart label you created
But, frankly, I do not understand why you are doing this steps. What are you traying to obtain? A list of all PC with this software and its version? - c.castellari 6 years ago-
Yea, there is probably an easier way to skin the cat. It was just scripts that I already had built. Getting the id from sc was necessary, because software metering needs to be enabled and that's the way to identify it. Solstice doesn't show up in the catalog, except for this weird back channel. - five. 6 years ago
