Merge two select queries

Question

Any assistance with merging the following two select queries would be greatly appreciated.

• The first query returns 1000 rows
• The second query returns 1000 rows.
• RSV.ResourceID is the primary key
• RSV.ResourceID by itself outputs 14,000 rows.

The end goal is to output all 14,000 rows for RSV.ResourceID and have it include a column with results for GBU.BrowserName0 and a column with results for GLD.Size0.

Select RSV.ResourceID, GBU.BrowserName0

From (
select ResourceID,
max(GBU.UsagePercentage0) as UsagePercentage0
from
v_GS_BROWSER_USAGE GBU
WHERE
GBU.UsagePercentage0 > 0
group by ResourceID)
as s

INNER JOIN v_GS_BROWSER_USAGE GBU on (GBU.Resourceid = s.ResourceID and GBU.UsagePercentage0 = s.UsagePercentage0)
RIGHT OUTER JOIN v_R_System_Valid RSV on GBU.Resourceid = RSV.ResourceID

___________________________________________

Select RSV.ResourceID, GLD.Size0

From (select ResourceID,
max(gld.deviceid0) as deviceid0
from
v_GS_LOGICAL_DISK GLD
WHERE gld.deviceid0='C:'
group by ResourceID)
as d

INNER JOIN v_GS_LOGICAL_DISK GLD on (GLD.Resourceid = d.ResourceID and GLD.deviceid0 = d.deviceid0)
RIGHT OUTER JOIN v_R_System_Valid RSV on GLD.ResourceID = RSV.ResourceID

Answer 1

I was able to work through this using an Outer Apply.

Answer 2

Hi @DonN ,

Welcome to the Microsoft TSQL Q&A Forum!

Do you mean there are 14000 rows in ResourceID in the v_R_System_Valid table?Is the number of rows returned by the following code 14000?

    Select ResourceID   
    from v_R_System_Valid   

You can try to combine the two queries with the following code：

Select RSV.ResourceID, GBU.BrowserName0,NULL,GLD.Size0  
From (  
select ResourceID,  
max(GBU.UsagePercentage0) as UsagePercentage0  
from  
v_GS_BROWSER_USAGE GBU  
WHERE  
GBU.UsagePercentage0 > 0  
group by ResourceID)as s  
INNER JOIN v_GS_BROWSER_USAGE GBU on (GBU.Resourceid = s.ResourceID and GBU.UsagePercentage0 = s.UsagePercentage0)  
RIGHT OUTER JOIN v_R_System_Valid RSV on GBU.Resourceid = RSV.ResourceID  
UNION ALL  
Select RSV.ResourceID,NULL, GLD.Size0  
From (select ResourceID,  
max(gld.deviceid0) as deviceid0  
from  
v_GS_LOGICAL_DISK GLD  
WHERE gld.deviceid0='C:'  
group by ResourceID)  
as d  
INNER JOIN v_GS_LOGICAL_DISK GLD on (GLD.Resourceid = d.ResourceID and GLD.deviceid0 = d.deviceid0)  
RIGHT OUTER JOIN v_R_System_Valid RSV on GLD.ResourceID = RSV.ResourceID  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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 3

Correct, ResourceID in the v_R_System_Valid table returns 14,000 rows. The challenge is that the query returned 28,000 rows. Would it be possible to instead have 14,000 rows returned? For example.

Current results utilizing the query above.

Desired results