Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

SQL Query to return 2 values of the same column with conditions



Hello,

I am trying to find a way to properly edit the below 2 SQL queries into a single query. What I want to do is return all values where es.name = widget a where value is less than 10440 AND where es.name = widget b where value is less than 10.  Since I am looking to report 2 different values from the same column(s), I can't find a way to combine this into a single query.
Any help is greatly appreciated.


QUERY1
select md.timestamp Time_Of_Poll, i.name Computer, es.name Metric,
md.numvalue Metric_Value from vMonitorMetricData md,
vItem i, Evt_Monitor_Metric_Status es
where md.resourceguid = i.guid and es.name = 'widget a'
and md.numvalue < '10440'
order by md.timestamp, es.name

QUERY 2
select md.timestamp Time_Of_Poll, i.name Computer, es.name Metric,
md.numvalue Metric_Value from vMonitorMetricData md,
vItem i, Evt_Monitor_Metric_Status es
where md.resourceguid = i.guid and es.name = 'widget b'
and md.numvalue < '10'
order by md.timestamp, es.name
0
Charlie_Melega
Asked:
Charlie_Melega
2 Solutions
 
CluskittCommented:
select md.timestamp Time_Of_Poll, i.name Computer, es.name Metric,
md.numvalue Metric_Value from vMonitorMetricData md,
vItem i, Evt_Monitor_Metric_Status es
where md.resourceguid = i.guid and ((es.name = 'widget a'
and md.numvalue < '10440') or (es.name = 'widget b'
and md.numvalue < '10'))
order by md.timestamp, es.name
0
 
Eugene ZCommented:
--try
select md.timestamp Time_Of_Poll, i.name Computer, es.name Metric,
md.numvalue Metric_Value from vMonitorMetricData md,
vItem i, Evt_Monitor_Metric_Status es
where md.resourceguid = i.guid and es.name = 'widget a'
and md.numvalue < '10440'
UNION ALL
select md.timestamp Time_Of_Poll, i.name Computer, es.name Metric,
md.numvalue Metric_Value from vMonitorMetricData md,
vItem i, Evt_Monitor_Metric_Status es
where md.resourceguid = i.guid and es.name = 'widget b'
and md.numvalue < '10'
order by md.timestamp, es.name
 
0
 
CluskittCommented:
In this case, a UNION is much less effective (meaning, slower) than a single query. It really is simple, seeing as only the conditions change. It's a simple AND/OR clause on WHERE. If there were different tables or views, then UNION might be better. But in this case, I don't think it's necessary. :)
0
 
David ToddSenior DBACommented:
Hi,

I noticed that you aren't using the ANSI join syntax

you wrote something like
select columns
from table1, table2, table3
where
  table1.col1 = table2.col2
  and table3.col3 = somevalue

compared to
select columns
from table1 t1
inner join table2 t2 on t2.col2 = t1.col1
inner join table3 t3 on t3.col3 = somevalue

Just that using the ANSI join syntax makes it easier to read and see what is a join and what is where condition.

HTH
  David
0
 
Charlie_MelegaAuthor Commented:
excellent feedack, Thanks all
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now