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