October 5, 2018 at 2:51 am
I am modifying a Query I have. (I am using dummy data below as the set i have is quite large but the premise is similar)
I have 2 tables.
Table 1 is a list of machines and applications installed on them. for example.
TBL_AUDIT
PC001 WORD
PC001 EXCEL
PC001 WIRESHARK
PC001 SOPHOS
PC002 WORD
PC002 WIRESHARK
I have another Table called Applications
TBL_APPLICATIONS
WORD WINDOWS 10 READY
WIRESHARK WINDOWS 10 READY
OUTLOOK NOT REQUIRED
I need a query that will show me a list of PC's only when all of the applications for a machine are present in the Application Table and where the applications are windows 10 ready and then to group y pc name. .
The Result of this query should be PC002
I have got this so far
SELECT pcs.MACHINE_NAME
FROM dbo.MACHINES$ pcs
LEFT JOIN dbo.APPLICATIONS$ apps
ON apps.APPLICATIONS = pcs.APPLICATIONS
WHERE apps.APP_STATUS ='WINDOWS 10 READY'
GROUP BY pcs.MACHINE_NAME
--HAVING COUNT(pcs.APPLICATION) <>(apps.APPLICAION)
The commented out line i think i am falling over at. i am not getting any results back.
October 5, 2018 at 3:51 am
Hi,
I'm not quite sure what you are looking for, but please try this:
IF OBJECT_ID('tempdb..#TBL_AUDIT') IS NOT NULL DROP TABLE #TBL_AUDIT
CREATE TABLE #TBL_AUDIT
(
PcName NVARCHAR(128) NOT NULL
,ApplicationName NVARCHAR(128) NOT NULL
);
IF OBJECT_ID('tempdb..#TBL_APPLICATIONS') IS NOT NULL DROP TABLE #TBL_APPLICATIONS
CREATE TABLE #TBL_APPLICATIONS
(
ApplicationName NVARCHAR(128) NOT NULL
,[Status] NVARCHAR(16) NOT NULL
);
INSERT INTO #TBL_AUDIT ( PcName, ApplicationName)
VALUES
('PC001' ,'WORD' )
,('PC001' ,'EXCEL' )
,('PC001' ,'WIRESHARK' )
,('PC001' ,'SOPHOS' )
,('PC002' ,'WORD' )
,('PC002' ,'WIRESHARK' )
;
INSERT INTO #TBL_APPLICATIONS (ApplicationName, [Status])
VALUES
('WORD' ,'WINDOWS 10 READY' )
,('WIRESHARK' ,'WINDOWS 10 READY' )
,('OUTLOOK' ,'NOT REQUIRED' )
;
WITH CountReadyApp AS (
SELECT COUNT(ApplicationName) As NoOfReady
FROM #TBL_APPLICATIONS
WHERE [Status] = 'WINDOWS 10 READY')
SELECT TblAudit.PcName
FROM #TBL_AUDIT AS TblAudit
LEFT JOIN #TBL_APPLICATIONS AS TblApp
ON TblAudit.ApplicationName = TblApp.ApplicationName
GROUP BY TblAudit.PcName
HAVING COUNT(TblAudit.PcName) = (SELECT NoOfReady FROM CountReadyApp);
Br.
Mike
October 5, 2018 at 4:03 am
Mike that does look great i think that would be ideal for the example my only concern is the real life situation i have about 460000 entries so would take a while to add all of the values to the script.
Will definatly utilise it for other stuff though .
Thanks Mike.
October 5, 2018 at 3:00 pm
SELECT pcs.MACHINE_NAME
FROM dbo.MACHINES$ pcs
LEFT JOIN dbo.APPLICATIONS$ apps
ON apps.APPLICATIONS = pcs.APPLICATIONS
AND apps.APP_STATUS ='WINDOWS 10 READY'
GROUP BY pcs.MACHINE_NAME
HAVING COUNT(pcs.APPLICATION) >=1
October 8, 2018 at 8:32 am
Thanks Joe, I am not sure if this query would do the trick as it would end up showing me all machines where there is a matching application in the windows 10 ready list. So even if a machine had one app it would show up where as what i am ideally trying to do is to list all machines from TBL_AUDIT (Grouped by machine name) where all of the applications installed on a machine are present in the TBL_APPLICATIONS.
Many thanks for you response Joe.
October 8, 2018 at 10:25 am
Andrew.weckermann - Friday, October 5, 2018 2:51 AMI am modifying a Query I have. (I am using dummy data below as the set i have is quite large but the premise is similar)I have 2 tables.
Table 1 is a list of machines and applications installed on them. for example.
TBL_AUDIT
PC001 WORD
PC001 EXCEL
PC001 WIRESHARK
PC001 SOPHOS
PC002 WORD
PC002 WIRESHARKI have another Table called Applications
TBL_APPLICATIONS
WORD WINDOWS 10 READY
WIRESHARK WINDOWS 10 READY
OUTLOOK NOT REQUIREDI need a query that will show me a list of PC's only when all of the applications for a machine are present in the Application Table and where the applications are windows 10 ready and then to group y pc name. .
The Result of this query should be PC002
I have got this so far
SELECT pcs.MACHINE_NAME
FROM dbo.MACHINES$ pcs
LEFT JOIN dbo.APPLICATIONS$ apps
ON apps.APPLICATIONS = pcs.APPLICATIONS
WHERE apps.APP_STATUS ='WINDOWS 10 READY'
GROUP BY pcs.MACHINE_NAME
--HAVING COUNT(pcs.APPLICATION) <>(apps.APPLICAION)
The commented out line i think i am falling over at. i am not getting any results back.
IF OBJECT_ID('tempdb..#MACHINES') IS NOT NULL DROP TABLE #MACHINES
CREATE TABLE #MACHINES
(
PcName NVARCHAR(128) NOT NULL
);
IF OBJECT_ID('tempdb..#TBL_AUDIT') IS NOT NULL DROP TABLE #TBL_AUDIT
CREATE TABLE #TBL_AUDIT
(
PcName NVARCHAR(128) NOT NULL
,ApplicationName NVARCHAR(128) NOT NULL
);
IF OBJECT_ID('tempdb..#TBL_APPLICATIONS') IS NOT NULL DROP TABLE #TBL_APPLICATIONS
CREATE TABLE #TBL_APPLICATIONS
(
ApplicationName NVARCHAR(128) NOT NULL
,[Status] NVARCHAR(16) NOT NULL
);
INSERT INTO #MACHINES ( PcName)
VALUES
('PC001')
,('PC002')
,('PC003')
INSERT INTO #TBL_AUDIT ( PcName, ApplicationName)
VALUES
('PC001' ,'WORD' )
,('PC001' ,'EXCEL' )
,('PC001' ,'WIRESHARK' )
,('PC001' ,'SOPHOS' )
,('PC002' ,'WORD' )
,('PC002' ,'WIRESHARK' )
,('PC003' ,'SOPHOS' )
,('PC003' ,'WORD' )
;
INSERT INTO #TBL_APPLICATIONS (ApplicationName, [Status])
VALUES
('WORD' ,'WINDOWS 10 READY' )
,('WIRESHARK' ,'WINDOWS 10 READY' )
,('OUTLOOK' ,'NOT REQUIRED' )
;
GO
SELECT * FROM #MACHINES m
SELECT * FROM #TBL_APPLICATIONS
SELECT * FROM #TBL_AUDIT
GO
SELECT *
FROM #MACHINES m
WHERE (SELECT COUNT(*)
FROM (SELECT a.ApplicationName
FROM #TBL_APPLICATIONS a
/*WHERE a.Status = 'WINDOWS 10 READY'*/
EXCEPT
SELECT aud.ApplicationName
FROM #TBL_AUDIT aud
WHERE aud.PcName = m.PcName) A)=0
If you want only machines that have all applictions installed then:SELECT *
FROM #MACHINES m
WHERE NOT EXISTS(SELECT a.ApplicationName
FROM #TBL_APPLICATIONS a
/*WHERE a.Status = 'WINDOWS 10 READY'*/
EXCEPT
SELECT aud.ApplicationName
FROM #TBL_AUDIT aud
WHERE aud.PcName = m.PcName)
October 9, 2018 at 9:16 am
What the OP has asked for is a list of machines that for ALL of the apps installed on that machine, that all of those appear in the APPLICATIONS table with a status of WINDOWS 10 READY'. That's definitely not a machine that has ALL the apps in the applications table installed on it, because the status of all apps might not be what is needed. Let's try this:SELECT DISTINCT PCS.MACHINE_NAME
FROM dbo.[MACHINES$] AS PCS
WHERE (
SELECT COUNT(DISTINCT APPS.APPLICATIONS)
FROM dbo.[APPLICATIONS$] AS APPS
WHERE APPS.MACHINE_NAME = PCS.MACHINE_NAME
AND APPS.APPLICATIONS = PCS.APPLICATIONS
AND APPS.APP_STATUS = 'WINDOWS 10 READY'
) = (
SELECT COUNT(DISTINCT M.APPLICATIONS)
FROM dbo.[MACHINE$] AS M
WHERE M.MACHINE_NAME = PCS.MACHINE_NAME
);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 9, 2018 at 10:01 am
Relational Division. I've kept this example handy for a while now...
-- Simple example of relational division
-- Set up some sample data
DROP TABLE #ItemsMap; CREATE TABLE #ItemsMap (TagID INT, ItemID INT)
INSERT INTO #ItemsMap (TagID,ItemID) VALUES
(1,284838), (1,291475), (1,291480), (8,284838), (8,291475), (8,291475), (8,291480), (10,284838), (10,291480), (62,291475)
CREATE CLUSTERED INDEX cx_ItemsMap ON #ItemsMap (ItemID, TagID)
DROP TABLE #Probe; CREATE TABLE #Probe (TagID INT)
INSERT INTO #Probe (TagID) VALUES (1),(8),(62)
SELECT *
FROM #ItemsMap im
LEFT JOIN #Probe p
ON p.TagID = im.TagID
ORDER BY ItemID
-- relational division query
SELECT d.ItemID
FROM (
SELECT im.ItemID, im.TagID
FROM #ItemsMap im
INNER JOIN #Probe p ON p.TagID = im.TagID
GROUP BY im.ItemID, im.TagID
) d
GROUP BY d.ItemID
HAVING COUNT(*) = (SELECT COUNT(*) FROM #Probe)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 10, 2018 at 2:38 am
Thanks for your reply <a title="Go to ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl08_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork" style="text-decoration: none; color: rgb(34, 85, 136); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@Work unfortunate this code relies as per another post on me entering in manually values but as stated previously the example only has a few values but the actually number of records i need to apply this to is around 500000 so it could take alot of time to populate.
Thanks anyway.
October 10, 2018 at 2:51 am
Andrew.weckermann - Wednesday, October 10, 2018 2:38 AMThanks for your reply ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl08_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork" style="text-decoration: none; color: rgb(34, 85, 136); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@Work unfortunate this code relies as per another post on me entering in manually values but as stated previously the example only has a few values but the actually number of records i need to apply this to is around 500000 so it could take alot of time to populate.Thanks anyway.
Heh no problem Andrew. My post doesn't offer you a solution, let alone suggest you type in 500,000 rows into a temp table! Your problem domain has a name and it's fairly well documented online - it's a "Relational Division" problem. The code I posted is a very typical example and also provides some insight into how you might tackle the problem.
Note that folks on ssc will often provide solutions which include creating and populating temp tables. These are usually NOT part of the actual solution, they're just a source of data for the query to run against.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 10, 2018 at 2:53 am
sgmunson - Tuesday, October 9, 2018 9:16 AMWhat the OP has asked for is a list of machines that for ALL of the apps installed on that machine, that all of those appear in the APPLICATIONS table with a status of WINDOWS 10 READY'. That's definitely not a machine that has ALL the apps in the applications table installed on it, because the status of all apps might not be what is needed. Let's try this:SELECT DISTINCT PCS.MACHINE_NAME
FROM dbo.[MACHINES$] AS PCS
WHERE (
SELECT COUNT(DISTINCT APPS.APPLICATIONS)
FROM dbo.[APPLICATIONS$] AS APPS
WHERE APPS.MACHINE_NAME = PCS.MACHINE_NAME
AND APPS.APPLICATIONS = PCS.APPLICATIONS
AND APPS.APP_STATUS = 'WINDOWS 10 READY'
) = (
SELECT COUNT(DISTINCT M.APPLICATIONS)
FROM dbo.[MACHINE$] AS M
WHERE M.MACHINE_NAME = PCS.MACHINE_NAME
);
Steve I feel that your post maybe the closest to what i am looking for however the issue I have is that MACHINE_NAME only exists on TBL_AUDIT there is no MACHINE_NAME field in the TBL_APPLICATIONS. so just to confirm. For every application installed on a machine there is a separate record in the TBL_AUDIT table. there is a list of applications in TBL_APPLICATIONS that are proven to work on Windows 10. I need to highlight all machines were every application installed (LISTED IN TBL_AUDIT) is present in TBL_APPLICATIONS. the most important thing is that all applications need to be present in TBL_APPLICATIONS not just a few.
Thankyou
October 10, 2018 at 3:31 am
-- identify machines which have an unlisted app
;WITH Unlisted AS (
SELECT au.MACHINE_NAME, au.[application]
FROM TBL_AUDIT au
WHERE NOT EXISTS (
SELECT 1 FROM TBL_APPLICATIONS ap
WHERE ap.[application] = au.[application])
)
-- use it as an exclusion list
SELECT au.*
FROM TBL_AUDIT au
WHERE NOT EXISTS (SELECT 1 FROM Unlisted ul WHERE ul.MACHINE_NAME = au.MACHINE_NAME)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 10, 2018 at 4:30 am
ChrisM@Work - Wednesday, October 10, 2018 3:31 AM-- identify machines which have an unlisted app
;WITH Unlisted AS (
SELECT au.MACHINE_NAME, au.[application]
FROM TBL_AUDIT au
WHERE NOT EXISTS (
SELECT 1 FROM TBL_APPLICATIONS ap
WHERE ap.[application] = au.[application])
)
-- use it as an exclusion list
SELECT au.*
FROM TBL_AUDIT au
WHERE NOT EXISTS (SELECT 1 FROM Unlisted ul WHERE ul.MACHINE_NAME = au.MACHINE_NAME)
Thanks for the reply, I am guessing in need to modify your first query including the above changes? apologies i am no TSQL guru 🙂 and thanks for the reply.
October 10, 2018 at 4:51 am
Andrew.weckermann - Wednesday, October 10, 2018 4:30 AMChrisM@Work - Wednesday, October 10, 2018 3:31 AM-- identify machines which have an unlisted app
;WITH Unlisted AS (
SELECT au.MACHINE_NAME, au.[application]
FROM TBL_AUDIT au
WHERE NOT EXISTS (
SELECT 1 FROM TBL_APPLICATIONS ap
WHERE ap.[application] = au.[application])
)
-- use it as an exclusion list
SELECT au.*
FROM TBL_AUDIT au
WHERE NOT EXISTS (SELECT 1 FROM Unlisted ul WHERE ul.MACHINE_NAME = au.MACHINE_NAME)
Thanks for the reply, I am guessing in need to modify your first query including the above changes? apologies i am no TSQL guru 🙂 and thanks for the reply.
This query ^^ is very close to what you want, you might only need to change table and column names to match what you actually have.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply