If you want to combine the two result sets into one, then you can use a UNION (ALL), see
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver15 => Examples
SCCM - WQL fuse 2 query
First, sorry for my English ;)
I don't know a thing of WQL... But I need to fuse two queries in a single one.
1)
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from
SMS_R_System inner join SMS_G_System_DCMDeploymentState on SMS_G_System_DCMDeploymentState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_DCMDeploymentState.BaselineID = "ScopeId_B3CE35F3-4C3B-4B65-8791-1B03FABE9F99/Baseline_20b206e9-3bed-471d-8ca9-7efa3bd8d7b9"
and SMS_G_System_DCMDeploymentState.ComplianceState = "1"
2)
select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,
SYS.ResourceDomainORWorkgroup,SYS.Client
from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as
offer on sys.ResourceID=offer.ResourceID
WHERE AdvertisementID = 'PR12019D' and LastStateName = 'No Status'
Can you help me?
Thank you.
3 answers
Sort by: Oldest
-
Olaf Helper 40,901 Reputation points
2022-05-05T07:29:26.933+00:00 -
Bert Zhou-msft 3,421 Reputation points
2022-05-06T02:01:30.24+00:00 Hi,@Arnaud DURAND
Welcome to Microsoft T-SQL Q&A Forum!Yes, as olaf said , you can use union to find all records . I see that the fields queried in your table above are the same as the ones below , and I prefer to link the three tables first , and in the search field, maybe you can try this:
select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier, SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System sys inner join SMS_G_System_DCMDeploymentState as DCM on DCM.ResourceID = sys.ResourceId left join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID where AdvertisementID = 'PR12019D' and LastStateName = 'No Status' AND DCM.BaselineID = "ScopeId_B3CE35F3-4C3B-4B65-8791-1B03FABE9F99/Baseline_20b206e9-3bed-471d-8ca9-7efa3bd8d7b9" AND DCM.ComplianceState = "1"
Reminder: It is recommended to alias the table, it will help you read the code, the name is too long and easy to forget.
Best regards,
Bert Zhou
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread. -
Arnaud DURAND 1 Reputation point
2022-05-06T08:28:34.57+00:00 Thanks for your reply ;)
When a try your query in SCCM I've got an error "The query statement that you entered is not valid"