How to join two sql queries together?
-
Hello, I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query. Here are the queries: Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE -
Hello, I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query. Here are the queries: Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERERead up on
UnionorUnion AllNever underestimate the power of human stupidity RAH
-
Hello, I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query. Here are the queries: Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHEREYou can use "Except Distinct" !
-
Hello, I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query. Here are the queries: Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE -
Hello, I have tried inserting those commands into various locations in the query, without success. Could someone give me an example please?
-
Hello, I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query. Here are the queries: Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE -
Someone helped me come up with this code today, it appears to be producing the proper results, what does every one think? It uses And UserName Not In, and brings in the second search to compare.
-- Mailbox size query users who are set to unlimited, distinct username field with disabled mailboxes removed from search
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-08T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE _QA.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'