Skip to main content
Solved

MySQL Query to list all distinct applications

  • August 27, 2018
  • 1 reply
  • 0 views

Forum|alt.badge.img+6
  • Contributor
  • 24 replies

Hello! I'm looking to build a distinct list of applications from Macs within a certain building. I've modified a sample query to give me a list of all applications on all computers, but I don't want a report of computer names with all applications installed on them. I want a distinct list of applications.

SELECT DISTINCT a.computer_name AS 'Computer Name', c.application_name AS 'Application Name', c.application_version AS 'Version', c.application_path AS 'Application Path'
From computers_denormalized a INNER JOIN reports b ON a.computer_id = b.computer_id AND computer_name LIKE 'Building1%'
INNER JOIN applications c ON b.report_id = c.report_id
ORDER BY a.computer_name

Thanks! I appreciate the help.

Best answer by zanb

Hey again,

With a little bit of searching and RTFM-ing, I was able to learn about subqueries and ordering by the application name within the subquery to generate my report. Here's what I came up with:

SELECT DISTINCT r.g_app, r.g_vers, r.g_path FROM 
(
    SELECT DISTINCT a.computer_name AS 'g_name', 
                    c.application_name AS 'g_app', 
                    c.application_version AS 'g_vers', 
                                    c.application_path AS 'g_path'
    From computers_denormalized a  INNER JOIN reports b ON a.computer_id = b.computer_id AND computer_name LIKE 'Building1%'
    INNER JOIN applications c ON b.report_id = c.report_id
    ORDER BY c.application_name
) r
View original
Did this topic help you find an answer to your question?

1 reply

Forum|alt.badge.img+6
  • Author
  • Contributor
  • 24 replies
  • Answer
  • August 27, 2018

Hey again,

With a little bit of searching and RTFM-ing, I was able to learn about subqueries and ordering by the application name within the subquery to generate my report. Here's what I came up with:

SELECT DISTINCT r.g_app, r.g_vers, r.g_path FROM 
(
    SELECT DISTINCT a.computer_name AS 'g_name', 
                    c.application_name AS 'g_app', 
                    c.application_version AS 'g_vers', 
                                    c.application_path AS 'g_path'
    From computers_denormalized a  INNER JOIN reports b ON a.computer_id = b.computer_id AND computer_name LIKE 'Building1%'
    INNER JOIN applications c ON b.report_id = c.report_id
    ORDER BY c.application_name
) r

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings