SQL Server : nested looping over two Selects

Question

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?

Seems like a job for a FULL JOIN between both queries

Alejandro
– Alejandro

2018-03-05 18:17:04 +00:00
Commented Mar 5, 2018 at 18:17 — Alejandro
– Alejandro, Commented Mar 5, 2018 at 18:17
genius! will give it a try.

gunslingor
– gunslingor

2018-03-05 18:21:28 +00:00
Commented Mar 5, 2018 at 18:21 — gunslingor
– gunslingor, Commented Mar 5, 2018 at 18:21
That is not tsql

paparazzo
– paparazzo

2018-03-05 19:52:46 +00:00
Commented Mar 5, 2018 at 19:52 — paparazzo
– paparazzo, Commented Mar 5, 2018 at 19:52

Tab Alleman · Accepted Answer · 2018-03-05 18:22:37Z

1

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.

answered Mar 5, 2018 at 18:22

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : nested looping over two Selects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related