SQL Query Help

Discussion in 'Programming & Software Development' started by cjzdj, Jun 29, 2018.

  1. cjzdj

    cjzdj Member

    Joined:
    Apr 14, 2011
    Messages:
    9
    Location:
    Upper North Shore NSW
    I am hoping someone might be able to help me with a SQL query.

    What I am trying to do is pull the value_on_client, extension_attribute_id, computer_id and mac_address, Where we get the extension_attribute_id and value_on_client based on each computer_id's, maximum report_ID.

    Each computer_id has many report_id's, and we want to find the maximum report_id for each computer, then get the value_on_client and Extension_attribute_id form the Extension_attribute_values table.

    Then report those values back along with the computer ID and mac address


    For example the result I would like to get is this
    computer_id | mac_address | extension_attribute_id | value on client
    1 00:00:00:00:00:A2 7 NO
    1 00:00:00:00:00:A2 4 Null
    1 00:00:00:00:00:A2 23 File Exists
    2 00:00:00:00:00:8A 7 Yes
    2 00:00:00:00:00:8A 4 2018-06-20
    2 00:00:00:00:00:8A 23 File Exists
    3 00:00:00:00:00:6A 7 Yes
    3 00:00:00:00:00:6A 4 2018-9-9
    3 00:00:00:00:00:6A 23 Not Found


    There is 3 tables involved (# of columns have been cut down for simplicity).

    Computer Table
    Computer_ID(PK) | Computer Name | mac_address
    1 Eddie 00:00:00:00:00:A2
    2 Sarah 00:00:00:00:00:8A
    3 Kate 00:00:00:00:00:6A



    Reports Table
    report_id (PK) | computer_id
    100 1
    101 2
    102 3
    103 2
    104 3
    105 1



    Extension_attribute_values
    extension_attribute_id | report_id | value_on_client
    7 100 Yes
    4 100 2018-06-09
    23 100 File Exists
    7 105 No
    4 105 Null
    23 105 File Exists

    7 101 No
    4 101 2017-06-09
    23 101 Not Found
    7 103 Yes
    4 103 2018-06-20
    23 103 File Exists

    7 102 Yes
    4 102 2018-01-01
    23 102 File Exists
    7 104 Yes
    4 104 2018-9-9
    23 104 Not Found
     
  2. asho444

    asho444 Member

    Joined:
    Feb 7, 2012
    Messages:
    355
    Location:
    Buddina QLD
    have you tried using max?
    for example:

    Select distinct a.comp_id, a.mac_add, Max(b.report_id), c.value
    From com_table a
    Inner join reports_table b on a.comp_id = b.comp_id
    Inner join ext_table c on b.report_id = c.report_id
    Group by distinct a.comp_id, a.mac_add, c.value

    tho i think the c.value will through it out
     
  3. Dingostolemyghz

    Dingostolemyghz Member

    Joined:
    Mar 30, 2011
    Messages:
    745
    I would use a subquery to only give the max report id rows using the max function, then join to the other tables as normal - example of the subquery below

    Code:
    ( SELECT MAX(report_id) report_id
           , computer_id
        FROM reports_table
      GROUP BY computer_id )
     
    asho444 likes this.
  4. OP
    OP
    cjzdj

    cjzdj Member

    Joined:
    Apr 14, 2011
    Messages:
    9
    Location:
    Upper North Shore NSW
    Brilliant guys, thanks for the help and assistance.

    Dingostolemyghz exactly what I was looking for... So simple but for some reason just couldn't get my head around that one. Got it all working. Thanks
     

Share This Page