SCCM - WQL fuse 2 query

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

@Bert Zhou-msft

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"