This is how I setup my test database:
-- Computer assets
CREATE TABLE tblAssets
(
AssetID int NOT NULL,
AssetName nvarchar(200) NOT NULL,
UserName nvarchar(150),
PRIMARY KEY (AssetID)
);
-- Software titles
CREATE TABLE tblSoftwareUNI
(
SoftID int NOT NULL,
softwareName nvarchar(300),
PRIMARY KEY (SoftID)
);
-- Software installed on computer assets
CREATE TABLE tblSoftware
(
SoftwareID int NOT NULL,
AssetID int NOT NULL,
SoftID int NOT NULL,
SoftwareVersion nvarchar(100),
PRIMARY KEY (SoftwareID),
CONSTRAINT FK_tblSoftware_tblAssets
FOREIGN KEY (AssetID)
REFERENCES tblAssets(AssetID),
CONSTRAINT FK_tblSoftware_tblSoftwareUNI
FOREIGN KEY (SoftID)
REFERENCES tblSoftwareUNI(SoftID)
);
-- File existence on asset
CREATE TABLE tblFileVersions
(
VersionID int NOT NULL,
AssetID int NOT NULL,
FilePathfull nvarchar(1000),
Found bit,
PRIMARY KEY (VersionID),
CONSTRAINT FK_tblFileVersions_tblAssets
FOREIGN KEY (AssetID)
REFERENCES tblAssets(AssetID)
);
INSERT INTO tblSoftwareUNI (SoftID, softwareName) VALUES (1, 'Adobe Acrobat');
INSERT INTO tblSoftwareUNI (SoftID, softwareName) VALUES (2, 'Microsoft Word');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (1, 'PC1', 'Vito Corleone');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (2, 'PC2', 'Jack Sparrow');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (3, 'PC3', 'Han Solo');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (4, 'PC4', 'Anakin Skywalker');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (5, 'PC5', 'Harry Potter');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (1, 1, 1, '10.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (2, 2, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (3, 2, 2, '1.42');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (4, 2, 2, '2.57');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (5, 3, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (6, 3, 2, '2.57');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (7, 4, 1, '10.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (8, 4, 2, '1.40.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (9, 5, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (10, 5, 2, '1.40.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (11, 5, 2, '2.57');
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (1, 1, 'c:\temp\temp.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (2, 1, 'c:\test\testfile.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (3, 2, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (4, 2, 'c:\test\testfile.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (5, 3, 'c:\temp\temp.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (6, 3, 'c:\test\testfile.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (7, 4, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (8, 4, 'c:\test\testfile.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (9, 5, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (10, 5, 'c:\test\testfile.txt', 1);
I run this query:
SELECT
tblAssets.AssetName,
tblAssets.UserName,
CASE LEFT(tblSoftware.SoftwareVersion, 1)
WHEN '1' THEN tblSoftware.SoftwareVersion
ELSE ''
END AS v1,
CASE LEFT(tblSoftware.SoftwareVersion, 1)
WHEN '2' THEN tblSoftware.SoftwareVersion
ELSE ''
END AS v2,
(SELECT TOP 1 tblFileVersions.Found
FROM tblFileVersions
WHERE tblFileVersions.FilePathFull = 'c:\test\testfile.txt'
AND tblFileVersions.AssetID = tblSoftware.AssetID) FileFound
FROM
tblSoftware
INNER JOIN
tblAssets ON tblSoftware.AssetID = tblAssets.AssetID
INNER JOIN
tblSoftwareUNI ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblSoftwareUni.softwareName LIKE 'Microsoft%'
Which returns:
AssetName | UserName | v1 | v2 | FileFound |
---|---|---|---|---|
PC2 | Jack Sparrow | 1.42 | 1 | |
PC2 | Jack Sparrow | 2.57 | 1 | |
PC3 | Han Solo | 2.57 | 1 | |
PC4 | Anakin Skywalker | 1.40.9 | 0 | |
PC5 | Harry Potter | 1.40.9 | 1 |
I would like to have only 1 line for each asset, so like this:
AssetName | UserName | v1 | v2 | FileFound |
---|---|---|---|---|
PC2 | Jack Sparrow | 1.42 | 2.57 | 1 |
PC3 | Han Solo | 2.57 | 1 | |
PC4 | Anakin Skywalker | 1.40.9 | 0 | |
PC5 | Harry Potter | 1.40.9 | 1 |
I cannot get my query results to my satisfaction. Can you help me?
This is achievable using left joins.
Select t1.AssetName,
t1.UserName, t.SoftwareVersion, t4.SoftwareVersion,
(select top 1 tblFileVersions.Found from tblFileVersions where tblFileVersions.FilePathFull = 'c:\test\testfile.txt' And tblFileVersions.AssetID = t1.AssetID) FileFound
FROM tblAssets t1
LEFT JOIN
(select t2.AssetId, t3.SoftID, t2.SoftwareVersion from tblSoftware t2
inner join tblSoftwareUNI t3 on t3.SoftID = t2.SoftID
where t3.softwareName Like 'Microsoft%' and Left(t2.SoftwareVersion, 1)='1') t
on t.AssetId = t1.AssetId
LEFT JOIN
(select t2.AssetId, t3.SoftID, t2.SoftwareVersion from tblSoftware t2
inner join tblSoftwareUNI t3 on t3.SoftID = t2.SoftID
where t3.softwareName Like 'Microsoft%' and Left(t2.SoftwareVersion, 1)='2') t4
on t1.AssetId = t4.AssetId
WHERE isnull(t.SoftwareVersion,'') != '' or isnull(t4.SoftwareVersion, '') != ''
see dbfiddle