CODEWITHSUNDEEP
sql-serverloopsnestedsubquery
gunslingor
gunslingor

Reputation: 1476

SQL Server : nested looping over two Selects

I have the following two queries that produce the results I need. Now the final output I truly need I would usually use python for after the results are returned, but unfortunately only SQL can be used.

Query A:

SELECT * 
FROM openquery(PROD, 'SELECT `status`, computer_name, device_type 
                      FROM assets 
                      WHERE (device_type="SERVER") 
                        AND (status="ACTIVE")')

Query B:

SELECT * 
FROM openquery(AppMap, 'SELECT `t1`.`uaid` AS `uaid`, `t3`.`computer_name`,
                        FROM ((`applications` `t1` 
                        JOIN `app_infrastructure` `t2` ON (((`t1`.`uaid` = `t2`.`uaid`)))) 
                        JOIN `infrastructure` `t3` ON ((`t2`.`infrastructure_id` = `t3`.`infrastructure_id`)));')

How I would want to process the results:

if a computer_name is in both A and B:
   final_row = ['computer_name', 1]
elseif a computer_name is in A but not B:
   final_row = ['computer_name', 0]
elseif a computer_name is in B but not A:
   final_row = ['computer_name', 2]

So my final query results need to look like those rows, does that make sense?

Upvotes: 0

Views: 194

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

In a stored procedure, use both queries to load table variables.

Then do a FULL OUTER JOIN query, joining the two table variables on computer_name, and use a CASE expression to get your final_row value for each computer name.

Upvotes: 1

Related Questions