SQLTeam.com | Weblogs | Forums

Combine two select queries

#1

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

#2

hi

please see this .. UNION ALL operator

how it works..

select query 1
union all
select query 2

-- point to note
the number of columns and datatypes of columns have to match between select query 1 and select query 2

example
.. if you have 5 columns in select query 1
then you have to have 5 columns in select query 2

datatypes have to match
select int , varchar
union all
select int , varchar

if you need help implementing please let me know

#3

This was very helpful. Specifically, that the numbers of columns and datatypes had to match.

The first query above returns 14,000 rows.

The second query above also returns 14,000 rows.

The Union All then returns 28,000 rows. Would it be possible to have the data combined with 14,000 over lapping rows returned? For ex.

image

#4

Yes

instead of

UNION ALL

use

INTERSECT

select query 1
INTERSECT
select query 2

#5

Intersect only returns the row if the values in both columns match. As a result, only the nulls like the following are being returned.

image

The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

#6

It looks like you want to get the BrowserName for the browser with the highest usage - per resource, and the Size of the C: drive for that resource. I am assuming that v_GS_LOGICAL_DISK only has a single entry per drive per resource - so a single C: drive for each resource.

Something like this may work:

 Select rsv.ResourceID
      , gb.BrowserName0
      , gl.Size0
   From v_R_System_Valid                        rsv

  Cross Apply (Select Top 1
                      gbu.BrowserName0
                 From v_GS_BROWSER_USAGE        gbu
                Where gbu.ResourceID = rsv.ResourceID
                Order By
                      gbu.UsagePercentage0 desc
              )                                  gb

  Cross Apply (Select Top 1
                      gld.Size0
                 From v_GS_LOGICAL_DISK         gld
                Where gld.deviceid0 = 'C:'
                  And gld.ResourceID = rsv.ResourceID
                Order By
                      gld.Size0 desc
              )                                  gl;

Another option would be to just use simple joins - and get the top 1...

 Select Top 1 With Ties
   From v_R_System_Valid                rsv
  Inner Join v_GS_BROWSER_USAGE         gbu On gbu.ResourceID = rsv.ResourceID
  Inner Join v_GS_LOGICAL_DISK          gld On gld.ResourceID = rsv.ResourceID
                                           And gld.deviceid0 = 'C:'
  Order By
        row_number() over(Partition By rsv.ResourceID Order By gbu.BrowserName0 desc);

If you have entries in v_R_System_Valid that do not have entries in the other tables and you want those included in the results then change Cross Apply to Outer Apply in the first query - and Inner Join to Left Join in the second query.

1 Like
#7

The first query with the Outer Apply worked perfectly. Thank you for helping me with this.