find item that does not have a specific entry

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » find item that does not have a specific entry...

find item that does not have a specific entry

Rate Topic

Display Mode

Topic Options

Author

Message

lawson2305

Posted Monday, December 31, 2012 10:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

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.

Post #1401434

Lynn Pettis

Posted Monday, December 31, 2012 10:44 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

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

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

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1401447

lawson2305

Posted Monday, December 31, 2012 11:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

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.

Post #1401464

Lynn Pettis

Posted Monday, December 31, 2012 11:50 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1401467

Lynn Pettis

Posted Monday, December 31, 2012 12:00 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

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

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1401470

lawson2305

Posted Monday, December 31, 2012 12:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

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;

Post #1401483

Lynn Pettis

Posted Monday, December 31, 2012 1:17 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385


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

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1401490

lawson2305

Posted Monday, December 31, 2012 1:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

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?

Post #1401492

Lynn Pettis

Posted Monday, December 31, 2012 1:46 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

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 Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1401496

lawson2305

Posted Monday, December 31, 2012 1:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

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!

Post #1401500

« Prev Topic | Next Topic »

Permissions