Link to home
Create AccountLog in
Avatar of maximus81
maximus81Flag for United States of America

asked on

Need help with advanced MySQL query

Im trying to create a query that will list all the computers a piece of software is installed on. The systems are in there own table called system and identified by a number called system_id. The software is in the table sys_sw_software and the ID tied to the system_id is called system_id.

I would like to search for the name of a piece of software which is called software_name and then get a list of all the systems that its installed on from system table.
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of maximus81

ASKER

Thanks. This is what i am using:

select  
system.hostname,
sys_sw_software.software_name,
sys_sw_software.software_publisher,
sys_sw_software.software_version
from system
inner join sys_sw_software on sys_sw_software.system_id = system.system_id
where sys_sw_software.timestamp = system.timestamp
AND sys_sw_software.software_name = 'Software Name'
What does requiring the two timestamps equal accomplish. Just curious, as logically, I saw a simple join by system_id?
If you don't add the timestamps you get all the old data also. This ensures that you get the newest information from the database. Let say I deleted a piece of software off a machine and then I ran the audit script, without the timsetamps it would add that to the query.