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   [ + ] Show 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.

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
Answer this question or Comment on this question for clarity