Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find item that does not have a specific entry Expand / Collapse
Author
Message
Posted Monday, December 31, 2012 10:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:47 AM
Points: 61, Visits: 240
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
Posted Monday, December 31, 2012 10:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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





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
Posted Monday, December 31, 2012 11:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:47 AM
Points: 61, Visits: 240
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
Posted Monday, December 31, 2012 11:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.



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 #1401467
Posted Monday, December 31, 2012 12:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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

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 #1401470
Posted Monday, December 31, 2012 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:47 AM
Points: 61, Visits: 240
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
Posted Monday, December 31, 2012 1:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555

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

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 #1401490
Posted Monday, December 31, 2012 1:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:47 AM
Points: 61, Visits: 240
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
Posted Monday, December 31, 2012 1:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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

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 #1401496
Posted Monday, December 31, 2012 1:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:47 AM
Points: 61, Visits: 240
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 »

Add to briefcase

Permissions Expand / Collapse