December 31, 2012 at 10:23 am
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.
December 31, 2012 at 10:44 am
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.nameFROM 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
December 31, 2012 at 11:46 am
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.
December 31, 2012 at 11:50 am
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.
December 31, 2012 at 12:00 pm
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
December 31, 2012 at 12:45 pm
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;
December 31, 2012 at 1:17 pm
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
December 31, 2012 at 1:37 pm
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?
December 31, 2012 at 1:46 pm
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
December 31, 2012 at 1:59 pm
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