Charlie_Melega
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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. :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent feedack, Thanks 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 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