/bundles/itninjaweb/img/Breadcrumb_cap_w.png
All,

I wanted to run a quick query by everyone to see if it looks like I'm on the right track. I've done a little MySQL before, but I'm more than a little rusty so I wanted to have some other eyes look at this. I'm just trying to run a simple query to list out every software title that contains the text 'adobe', list the title, the version, and the host name its installed on.

What I have is:

SELECT SOFTWARE.ID, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSION, MACHINE.NAME, MACHINE.ID, MACHINE_SOFTWARE_JT.MACHINE_ID, MACHINE_SOFTWARE_JT.SOFTWARE_ID
FROM ORG1.SOFTWARE, ORG1.MACHINE, ORG1.MACHINE_SOFTWARE_JT
WHERE DISPLAY_NAME LIKE '%adobe%' and
MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID

The query runs, and at a glance it appears to return what I want, but it seemed maybe a little too simple since I wasn't using any explicit joins, etc. So I was doubting myself a bit.

Anyway if someone can let me know if this looks as you'd expect or if anything needs adjusting that would be great. Thanks.
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
You can modify my SQL "List machines with certain software titles" to have only '%adobe%'.

SELECT
MACHINE.NAME AS SYSTEM_NAME, IP,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME, ' ' ,SOFTWARE.DISPLAY_VERSION SEPARATOR '\n') AS SOFTWARE_TITLE_VERSION
from MACHINE LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
WHERE
SOFTWARE.DISPLAY_NAME like '%adobe%'
GROUP BY MACHINE.ID
ORDER by MACHINE.NAME,SOFTWARE.DISPLAY_NAME

Or you can also use this SQL to display software version displayed in its own column.


SELECT
MACHINE.NAME AS SYSTEM_NAME, IP,
SOFTWARE.DISPLAY_NAME AS SOFTWARE_TITLE_NAME,
SOFTWARE.DISPLAY_VERSION AS SOFTWARE_VERSION
from MACHINE LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
WHERE
SOFTWARE.DISPLAY_NAME like '%adobe%'
ORDER by MACHINE.NAME,SOFTWARE.DISPLAY_NAME, SOFTWARE_VERSION


I have yet to figure out how to group concat 2 columns at the same time
Answered 02/06/2010 by: wsteo
Senior Yellow Belt

Please log in to comment
0
Tim, without the joins you are just pulling all data from the tables. You need to join the tables to get valid data. Wsteo's examples will work.
Answered 02/06/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thank you for the replies. Running both my original query, and the updated one returns the same number of results. My query does return some extra columns I don't need, so I understand that it may not be as clean as the suggested query. But apart from cleaning up the output it doesn't appear to be significantly different, or am I misunderstanding?
Answered 02/08/2010 by: timantheos
Orange Senior Belt

Please log in to comment
0
The number of the results doesn't matter - the accuracy of the results does. Without joining the tables, the data is not aligning properly based on relationships.
Answered 02/08/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Tim, your query is syntactically fine and accurate: you are doing a cross-product join with WHERE clause elimination. The MySQL optimizer interprets this as an inner join so your query will also run quickly.

However, I do prefer the JOIN syntax that airwolf points out as I find it easier to read and to explain to others who are not familiar with SQL but might want to tweak a query.
Answered 02/20/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment