MySQL Query Check...
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.
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
Answers (5)
Please log in to answer
Posted by:
wsteo
14 years ago
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
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
Posted by:
airwolf
14 years ago
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.
Posted by:
timantheos
14 years ago
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?
Posted by:
airwolf
14 years ago
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.
Posted by:
GillySpy
14 years ago
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.
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.