Query Help - Left Join or Distinct Not Working

  • Hey All,

    I am no DBA by any means, I just do adhoc basic queries.

    Today I was given a task to pull a report for the following

    "All computers that begin with ARO and include the following in the same report. Domain, User, IP, Office Version, and Lotus Notes Version."

    I can't seem to be able to get all the info in a single query.

    Here are the 2 tables I have to get information from.

    Table 1

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Inv_Computer](

    [Guid] [uniqueidentifier] NOT NULL,

    [Name] [nvarchar](64) NULL,

    [Domain] [nvarchar](64) NOT NULL,

    [User] [nvarchar](64) NOT NULL,

    127.0.0.1 [nvarchar](16) NOT NULL

    ) ON [PRIMARY]

    GO

    Insert into inv_computer values ('9EAC8860-3965-4B87-8884-597449333963','ARODUS1XP1RF1','mydomain','AROWHudson','10.249.138.95')

    Insert into inv_computer values ('A6077480-8C50-4E8E-B0F1-606F93A4728E','ARODUS2VRG331','mydomain','AROKNox','10.48.40.198')

    Insert into inv_computer values ('6A2F6CBC-168E-452D-8F3C-D9E454E3DA0E','ARODUS3V6SKC1','mydomain','AROTMason','10.249.138.121')

    Insert into inv_computer values ('A0A142F9-F585-4356-BFFF-1967B957339F','ARODUS46BFK71','mydomain','ARODRodzen','10.224.149.191')

    Insert into inv_computer values ('E2733611-154A-41CA-98F5-527FD081D1CE','ARODUS5CKKG81','mydomain','AROSacUPS','10.224.173.16')

    Insert into inv_computer values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A8','ARODUS706VK81','mydomain','ARORaLopez','10.224.171.29')

    Insert into inv_computer values ('E2733611-154A-41CA-98F5-527FD081D1Cf','HOLDUS5CKKG82','mydomain','HOLLust1','10.224.173.17')

    Insert into inv_computer values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A9','HOLDUS706VK83','mydomain','HOLJuLopez','10.224.171.30')

    Table 2

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Inv_Add_Remove_Programs](

    [_ResourceGuid] [uniqueidentifier] NOT NULL,

    [Name] [nvarchar](255) NOT NULL,

    [Version] [nvarchar](64) NOT NULL

    ) ON [PRIMARY]

    GO

    Insert into Inv_Add_Remove_Programs values ('9EAC8860-3965-4B87-8884-597449333963','Lotus Notes','8.5.1')

    Insert into Inv_Add_Remove_Programs values ('A6077480-8C50-4E8E-B0F1-606F93A4728E','Microsoft Office Standard 2003','11.0.8173.0')

    Insert into Inv_Add_Remove_Programs values ('A6077480-8C50-4E8E-B0F1-606F93A4728E','Lotus Notes','8.5.1')

    Insert into Inv_Add_Remove_Programs values ('6A2F6CBC-168E-452D-8F3C-D9E454E3DA0E','Microsoft Office Progessional 2007','12.0.4518.1014')

    Insert into Inv_Add_Remove_Programs values ('A0A142F9-F585-4356-BFFF-1967B957339F','Microsoft Office Standard 2007','12.0.4518.1014')

    Insert into Inv_Add_Remove_Programs values ('E2733611-154A-41CA-98F5-527FD081D1CE','Lotus Notes','7.0.2')

    Insert into Inv_Add_Remove_Programs values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A8','Microsoft Office Standard 2003','11.0.8173.0')

    Insert into Inv_Add_Remove_Programs values ('E2733611-154A-41CA-98F5-527FD081D1Cf','Microsoft Office Professional 2007','12.0.4518.1014')

    Insert into Inv_Add_Remove_Programs values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A9','Microsoft Office Professional 2010','14.0.4763.1000')

    Insert into Inv_Add_Remove_Programs values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A9','Lotus Notes','7.0.2')

    Insert into Inv_Add_Remove_Programs values ('A0A142F9-F585-4356-BFFF-1967B957339F','Lotus Notes','7.0.2')

    I have tried queries like this to start with and all variations I can think of but I can't seem to get ALL 'ARO' computers along with the correct office and lotus versions.

    Select

    inv.Name,

    inv.Domain,

    inv.[User],

    inv.127.0.0.1,

    arp.Name,

    arp.Version

    From inv_computer inv

    left outer join inv_add_remove_programs arp on inv.Guid = arp._ResourceGuid

    Where

    inv.Name LIKE 'ARO%' AND

    (arp.Name LIKE 'Microsoft Office Standard%' OR inv.Name LIKE 'Microsoft Office Professional%')

    Order by inv.Name ASC

    Ultimately I need something that will give me this type of outcome if possible:

    NameDomainUserIP AddressOffice NameOffice VersionLotus NameLotus Version

    ARODUS1XP1RF1mydomainAROWHudson10.249.138.95Lotus Notes8.5.1

    ARODUS2VRG331mydomainAROKNox10.48.40.198Microsoft Office Standard 200311.0.8173.0Lotus Notes8.5.1

    ARODUS3V6SKC1mydomainAROTMason10.249.138.121Microsoft Office Progessional 200712.0.4518.1014

    ARODUS46BFK71mydomainARODRodzen10.224.149.191Microsoft Office Standard 200712.0.4518.1014Lotus Notes7.0.2

    ARODUS5CKKG81mydomainAROSacUPS10.224.173.16Lotus Notes7.0.2

    ARODUS706VK81mydomainARORaLopez10.224.171.29Microsoft Office Standard 200311.0.8173.0

    I can't think of any way to get both applications name and versions that apply to the computers that have both as well as return all ARO computers whether they have either installed or not.

    Any help/direction would be great.

    Thanks,

    Clay

  • How about this?

    SELECT

    inv.Name,

    inv.Domain,

    inv.[User],

    inv.127.0.0.1,

    MAX( CASE WHEN arp.Name LIKE '%Office%' THEN arp.Name END) AS OfficeName ,

    MAX( CASE WHEN arp.Name LIKE '%Office%' THEN arp.Version END ) AS OfficeVersion ,

    MAX( CASE WHEN arp.Name LIKE '%Lotus%' THEN arp.Name END ) AS LotusNotesName ,

    MAX( CASE WHEN arp.Name LIKE '%Lotus%' THEN arp.Version END ) AS LotusNotesVersion

    FROM inv_computer inv

    INNER JOIN inv_add_remove_programs arp

    ON inv.Guid = arp._ResourceGuid

    WHERE

    inv.Name LIKE 'ARO%'

    GROUP BY

    inv.Name,

    inv.Domain,

    inv.[User],

    inv.127.0.0.1

    ORDER BY inv.Name ASC

  • This should do it for you

    with msoffice as (

    Select

    Inv.Guid,

    inv.Name,

    inv.Domain,

    inv.[User],

    inv.127.0.0.1,

    case when arp.Name LIKE 'Microsoft Office%'

    Then arp.Name

    Else ''

    End as Office_Name,

    case when arp.name like 'Microsoft Office%'

    Then arp.Version

    Else ''

    End as OfficeVersion

    From inv_computer inv

    left outer join inv_add_remove_programs arp

    on inv.Guid = arp.ResourceGuid

    Where inv.Name LIKE 'ARO%'

    AND (arp.Name LIKE 'Microsoft Office%' or arp.Name is null)

    ), lotus as (

    Select

    Inv.Guid,

    inv.Name,

    inv.Domain,

    inv.[User],

    inv.127.0.0.1,

    case when arp.Name LIKE 'Lotus%'

    Then arp.Name

    Else ''

    End As Lotus_Name,

    Case when arp.Name like 'Lotus%'

    Then arp.Version

    Else ''

    End as Lotus_Version

    From inv_computer inv

    left outer join inv_add_remove_programs arp

    on inv.Guid = arp.ResourceGuid

    Where inv.Name LIKE 'ARO%'

    AND (arp.Name LIKE 'Lotus%' or arp.Name is null)

    )

    Select coalesce(L.name,m.Name) as CompName,coalesce(L.domain,m.Domain) as Domain

    ,coalesce(L.[User],m.[User]) as CompUser,coalesce(L.[Ip Address],m.127.0.0.1) as IPAddress

    ,m.Office_Name,m.OfficeVersion,L.Lotus_Name,L.Lotus_Version

    From msoffice m

    Full Outer Join lotus L

    on m.Guid = L.Guid

    Order by coalesce(l.name,m.Name) ASC

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Or you could do as ColdCoffee has written.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (3/28/2011)


    Or you could do as ColdCoffee has written.

    😉

  • I want to thank both Coffee and CirquedeSQLeil for the quick feedback.

    I would not have been able to come up with either of those queries.

    Great job.

    thanks again,

    Clay

  • You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply