Skip to main content
Solved

MySQL Query to list all distinct applications

  • August 27, 2018
  • 1 reply
  • 0 views
Z
Forum|alt.badge.img+6
  • zanb
  • 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

    Z
    Forum|alt.badge.img+6
    • zanb
    • 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


    Most helpful members this week

    Terms & ConditionsCookie settingsAccessibility statement

    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