find item that does not have a specific entry

  • Ok here is a sql I run that tells me all my machines that have java installed.

    SELECT DISTINCT i.guid, i.name

    FROM dbo.Inv_Installed_File_Details fd

    JOIN vComputer i ON i.Guid = fd._ResourceGuid

    where fd.path LIKE 'c:\Program files%\Java\%' and fd.name = 'java.exe'

    Order by i.name

    I would like to reverse this and get the results that do not match the where clause. I need help in writing figuring this out. I have played around with some things that just don't seem to give me what I need.

    Thanks for any help you can provide.

  • lawson2305 (12/31/2012)


    Ok here is a sql I run that tells me all my machines that have java installed.

    SELECT DISTINCT i.guid, i.name

    FROM dbo.Inv_Installed_File_Details fd

    JOIN vComputer i ON i.Guid = fd._ResourceGuid

    where fd.path LIKE 'c:\Program files%\Java\%' and fd.name = 'java.exe'

    Order by i.name

    I would like to reverse this and get the results that do not match the where clause. I need help in writing figuring this out. I have played around with some things that just don't seem to give me what I need.

    Thanks for any help you can provide.

    You mean like this:

    SELECT DISTINCT i.guid, i.name

    FROM

    dbo.Inv_Installed_File_Details fd

    JOIN vComputer i

    ON i.Guid = fd._ResourceGuid

    where

    NOT(fd.path LIKE 'c:\Program files%\Java\%' and fd.name = 'java.exe')

    Order by i.name

  • This is returning every system in the dB including the ones which my query locates.

    I think this needs to basically bring up all systems distinct and remove what is found in my query.

  • lawson2305 (12/31/2012)


    This is returning every system in the dB including the ones which my query locates.

    I think this needs to basically bring up all systems distinct and remove what is found in my query.

    Sorry, but no details, I can't test my query.

    Try reading and following the instructions provided in the first article I reference below in my signature block. You will get better answers if you provide more detailed information.

  • This seems to work on a single table query:

    CREATE TABLE dbo.InvInstalledFileDetails(

    FileDetailsID INT IDENTITY(1,1),

    FPath VARCHAR(128),

    PrgName VARCHAR(128)

    );

    GO

    INSERT INTO dbo.InvInstalledFileDetails(FPath, PrgName)

    VALUES ('c:\Program files\Java\Programs','java.exe'),

    ('c:\Program files\SQL','sqldb.exe');

    GO

    SELECT * FROM dbo.InvInstalledFileDetails;

    GO

    SELECT * FROM dbo.InvInstalledFileDetails fd

    WHERE fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe';

    GO

    SELECT * FROM dbo.InvInstalledFileDetails fd

    WHERE NOT(fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe');

    GO

    DROP TABLE dbo.InvInstalledFileDetails;

    GO

  • ok doing my best here to duplicate what you are asking:

    CREATE TABLE dbo.vComputer(

    Guid VARCHAR(128),

    Name VARCHAR(128)

    );

    GO

    INSERT INTO dbo.vcomputer(Guid, name)

    VALUES ('333','PC1'),

    ('222','pc2'),

    ('111','PC3');

    GO;

    CREATE TABLE dbo.InvInstalledFileDetails(

    FileDetailsID INT IDENTITY(1,1),

    _ResourceGuid varchar(128),

    FPath VARCHAR(128),

    PrgName VARCHAR(128)

    );

    GO

    INSERT INTO dbo.InvInstalledFileDetails(_ResourceGuid, FPath, PrgName)

    VALUES (333'c:\Program files\Java\Programs','java.exe'),

    (333'c:\Program files\SQL','sqldb.exe'),

    (222'c:\Program files\Java\Programs','java.exe'),

    (111'c:\Program files\SQL','sqldb.exe'),

    (222'c:\Program files\SQL','sqldb.exe');

    GO;

  • CREATE TABLE dbo.vComputer(

    Guid VARCHAR(128),

    Name VARCHAR(128)

    );

    GO

    INSERT INTO dbo.vcomputer(Guid, name)

    VALUES ('333','PC1'),

    ('222','pc2'),

    ('111','PC3');

    GO

    CREATE TABLE dbo.InvInstalledFileDetails(

    FileDetailsID INT IDENTITY(1,1),

    ResourceGuid varchar(128),

    FPath VARCHAR(128),

    PrgName VARCHAR(128)

    );

    GO

    INSERT INTO dbo.InvInstalledFileDetails(ResourceGuid, FPath, PrgName)

    VALUES

    ('333','c:\Program files\Java\Programs','java.exe'),

    ('333','c:\Program files\SQL','sqldb.exe'),

    ('222','c:\Program files\Java\Programs','java.exe'),

    ('111','c:\Program files\SQL','sqldb.exe'),

    ('222','c:\Program files\SQL','sqldb.exe');

    GO

    SELECT

    vc.Guid,

    vc.Name,

    fd.FileDetailsID,

    fd.ResourceGuid,

    fd.FPath,

    fd.PrgName

    FROM

    dbo.vComputer vc

    INNER JOIN dbo.InvInstalledFileDetails fd

    ON (vc.Guid = fd.ResourceGuid)

    ;

    GO

    SELECT

    vc.Guid,

    vc.Name,

    fd.FileDetailsID,

    fd.ResourceGuid,

    fd.FPath,

    fd.PrgName

    FROM

    dbo.vComputer vc

    INNER JOIN dbo.InvInstalledFileDetails fd

    ON (vc.Guid = fd.ResourceGuid)

    WHERE

    fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe';

    GO

    SELECT

    vc.Guid,

    vc.Name,

    fd.FileDetailsID,

    fd.ResourceGuid,

    fd.FPath,

    fd.PrgName

    FROM

    dbo.vComputer vc

    INNER JOIN dbo.InvInstalledFileDetails fd

    ON (vc.Guid = fd.ResourceGuid)

    WHERE

    NOT(fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe');

    GO

    DROP TABLE dbo.vComputer;

    DROP TABLE dbo.InvInstalledFileDetails;

    GO

  • I'm looking to only see this data:

    ('111','PC1')

    which I'm not getting from the query as it seems like what it is doing is including all results where java is not found so from our data it appears to be returning:

    ('333','PC1'),

    ('222','pc2'),

    ('111','PC3')

    because of these entries:

    (333'c:\Program files\SQL','sqldb.exe'),

    (222'c:\Program files\SQL','sqldb.exe');

    are you not seeing the same results?

  • You were asking the wrong question and it would have helped if you posted the DDL for the tables, the sample data, and expected results to begin with. Look at the last query.

    CREATE TABLE dbo.vComputer(

    Guid VARCHAR(128),

    Name VARCHAR(128)

    );

    GO

    INSERT INTO dbo.vcomputer(Guid, name)

    VALUES ('333','PC1'),

    ('222','pc2'),

    ('111','PC3');

    GO

    CREATE TABLE dbo.InvInstalledFileDetails(

    FileDetailsID INT IDENTITY(1,1),

    ResourceGuid varchar(128),

    FPath VARCHAR(128),

    PrgName VARCHAR(128)

    );

    GO

    INSERT INTO dbo.InvInstalledFileDetails(ResourceGuid, FPath, PrgName)

    VALUES

    ('333','c:\Program files\Java\Programs','java.exe'),

    ('333','c:\Program files\SQL','sqldb.exe'),

    ('222','c:\Program files\Java\Programs','java.exe'),

    ('111','c:\Program files\SQL','sqldb.exe'),

    ('222','c:\Program files\SQL','sqldb.exe');

    GO

    SELECT

    vc.Guid,

    vc.Name,

    fd.FileDetailsID,

    fd.ResourceGuid,

    fd.FPath,

    fd.PrgName

    FROM

    dbo.vComputer vc

    INNER JOIN dbo.InvInstalledFileDetails fd

    ON (vc.Guid = fd.ResourceGuid)

    ;

    GO

    SELECT

    vc.Guid,

    vc.Name,

    fd.FileDetailsID,

    fd.ResourceGuid,

    fd.FPath,

    fd.PrgName

    FROM

    dbo.vComputer vc

    INNER JOIN dbo.InvInstalledFileDetails fd

    ON (vc.Guid = fd.ResourceGuid)

    WHERE

    fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe';

    GO

    SELECT

    vc.Guid,

    vc.Name,

    fd.FileDetailsID,

    fd.ResourceGuid,

    fd.FPath,

    fd.PrgName

    FROM

    dbo.vComputer vc

    INNER JOIN dbo.InvInstalledFileDetails fd

    ON (vc.Guid = fd.ResourceGuid)

    WHERE

    NOT(fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe');

    GO

    SELECT

    vc.Guid,

    vc.Name

    FROM

    dbo.vComputer vc

    WHERE

    NOT EXISTS(SELECT 1

    FROM dbo.InvInstalledFileDetails fd

    WHERE fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe' AND vc.Guid = fd.ResourceGuid);

    GO

    DROP TABLE dbo.vComputer;

    DROP TABLE dbo.InvInstalledFileDetails;

    GO

  • Lynn this looks pretty good I have to review it a bit more but so far it looks like this is exactly what I'm looking for.

    Thanks, sorry for being a pain. Hope you have a Happy New Year!

Viewing 10 posts - 1 through 9 (of 9 total)

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