InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 14:34:45 GMT20http://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxLynn 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!Mon, 31 Dec 2012 13:59:14 GMTlawson2305http://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxYou 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.[code="sql"]CREATE TABLE dbo.vComputer( Guid VARCHAR(128), Name VARCHAR(128));GOINSERT INTO dbo.vcomputer(Guid, name)VALUES ('333','PC1'),('222','pc2'),('111','PC3');GOCREATE TABLE dbo.InvInstalledFileDetails( FileDetailsID INT IDENTITY(1,1), ResourceGuid varchar(128), FPath VARCHAR(128), PrgName VARCHAR(128));GOINSERT 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');GOSELECT vc.Guid, vc.Name, fd.FileDetailsID, fd.ResourceGuid, fd.FPath, fd.PrgNameFROM dbo.vComputer vc INNER JOIN dbo.InvInstalledFileDetails fd ON (vc.Guid = fd.ResourceGuid);GOSELECT vc.Guid, vc.Name, fd.FileDetailsID, fd.ResourceGuid, fd.FPath, fd.PrgNameFROM 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';GOSELECT vc.Guid, vc.Name, fd.FileDetailsID, fd.ResourceGuid, fd.FPath, fd.PrgNameFROM 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');GOSELECT vc.Guid, vc.NameFROM dbo.vComputer vcWHERE 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);GODROP TABLE dbo.vComputer;DROP TABLE dbo.InvInstalledFileDetails;GO[/code]Mon, 31 Dec 2012 13:46:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxI'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?Mon, 31 Dec 2012 13:37:23 GMTlawson2305http://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspx[code="sql"]CREATE TABLE dbo.vComputer( Guid VARCHAR(128), Name VARCHAR(128));GOINSERT INTO dbo.vcomputer(Guid, name)VALUES ('333','PC1'),('222','pc2'),('111','PC3');GOCREATE TABLE dbo.InvInstalledFileDetails( FileDetailsID INT IDENTITY(1,1), ResourceGuid varchar(128), FPath VARCHAR(128), PrgName VARCHAR(128));GOINSERT 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');GOSELECT vc.Guid, vc.Name, fd.FileDetailsID, fd.ResourceGuid, fd.FPath, fd.PrgNameFROM dbo.vComputer vc INNER JOIN dbo.InvInstalledFileDetails fd ON (vc.Guid = fd.ResourceGuid);GOSELECT vc.Guid, vc.Name, fd.FileDetailsID, fd.ResourceGuid, fd.FPath, fd.PrgNameFROM 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';GOSELECT vc.Guid, vc.Name, fd.FileDetailsID, fd.ResourceGuid, fd.FPath, fd.PrgNameFROM 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');GODROP TABLE dbo.vComputer;DROP TABLE dbo.InvInstalledFileDetails;GO[/code]Mon, 31 Dec 2012 13:17:43 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxok doing my best here to duplicate what you are asking:CREATE TABLE dbo.vComputer( Guid VARCHAR(128), Name VARCHAR(128));GOINSERT 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));GOINSERT 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;Mon, 31 Dec 2012 12:45:48 GMTlawson2305http://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxThis seems to work on a single table query:[code="sql"]CREATE TABLE dbo.InvInstalledFileDetails( FileDetailsID INT IDENTITY(1,1), FPath VARCHAR(128), PrgName VARCHAR(128));GOINSERT INTO dbo.InvInstalledFileDetails(FPath, PrgName)VALUES ('c:\Program files\Java\Programs','java.exe'), ('c:\Program files\SQL','sqldb.exe');GOSELECT * FROM dbo.InvInstalledFileDetails;GOSELECT * FROM dbo.InvInstalledFileDetails fdWHERE fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe';GOSELECT * FROM dbo.InvInstalledFileDetails fdWHERE NOT(fd.FPath LIKE 'c:\Program files%\Java\%' AND fd.PrgName = 'java.exe');GODROP TABLE dbo.InvInstalledFileDetails;GO[/code]Mon, 31 Dec 2012 12:00:23 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspx[quote][b]lawson2305 (12/31/2012)[/b][hr]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.[/quote]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.Mon, 31 Dec 2012 11:50:42 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxThis 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.Mon, 31 Dec 2012 11:46:08 GMTlawson2305http://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspx[quote][b]lawson2305 (12/31/2012)[/b][hr]Ok here is a sql I run that tells me all my machines that have java installed.[code="sql"]SELECT DISTINCT i.guid, i.name FROM dbo.Inv_Installed_File_Details fdJOIN vComputer i ON i.Guid = fd._ResourceGuidwhere fd.path LIKE 'c:\Program files%\Java\%' and fd.name = 'java.exe'Order by i.name[/code]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.[/quote]You mean like this:[code="sql"]SELECT DISTINCT i.guid, i.nameFROM dbo.Inv_Installed_File_Details fd JOIN vComputer i ON i.Guid = fd._ResourceGuidwhere NOT(fd.path LIKE 'c:\Program files%\Java\%' and fd.name = 'java.exe')Order by i.name[/code]Mon, 31 Dec 2012 10:44:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1401434-338-1.aspxOk here is a sql I run that tells me all my machines that have java installed.[code="sql"]SELECT DISTINCT i.guid, i.name FROM dbo.Inv_Installed_File_Details fdJOIN vComputer i ON i.Guid = fd._ResourceGuidwhere fd.path LIKE 'c:\Program files%\Java\%' and fd.name = 'java.exe'Order by i.name[/code]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.Mon, 31 Dec 2012 10:23:22 GMTlawson2305