CODEWITHSUNDEEP
sqlsql-server
Satish
Satish

Reputation: 17447

Select syntax inside WHERE

How do I use SELECT syntax inside WHERE for example I have following code:

SELECT 
    blah...blah..
    ...
WHERE  
(
  (APM_AlertsAndReportsData.ApplicationName = 'instance: tomcat6_noram (SNMP)') AND 
  (APM_AlertsAndReportsData.ComponentName = 'Memory Heap Used (B)') AND 
  (APM_AlertsAndReportsData.StatisticData >= 1908932600)
)

Here I am comparing values if its greater than or equal to 1908932600. I want to use SELECT in place of 1908932600 so it will automatically compare values rather than statically code in query.

Upvotes: 3

Views: 14743

Answers (3)

Mark Byers
Mark Byers

Reputation: 838416

You can use a subquery, you just need parentheses around it:

APM_AlertsAndReportsData.StatisticData >= (SELECT ... FROM ...)

Note that it may run slowly depending on what you are doing in your subquery. It might be better to use a JOIN instead.

Upvotes: 5

Luke101
Luke101

Reputation: 65268

here is a simple one:

Single value subquery

select * from Table1
where id = (select id from Table2 where Name = 'cool')

Muti Value subquery

select * from Table1
where id IN (select id from Table2 where Name LIKE 'A%')

select * from Table1
where id NOT IN (select id from Table2 where Name LIKE 'A%')

Corralated subquery

select * from Table1
where exists (select 1 from Table2 where Table1.id = Table2.id)

Upvotes: 2

ssis_ssiSucks
ssis_ssiSucks

Reputation: 1506

You need a "correlated subquery" in the WHERE clause:

WHERE  
(
  (APM_AlertsAndReportsData.ApplicationName = 'instance: tomcat6_noram (SNMP)') AND 
  (APM_AlertsAndReportsData.ComponentName = 'Memory Heap Used (B)') AND 
  (APM_AlertsAndReportsData.StatisticData >= (SELECT SomeValue FROM SomeTable))
)

Upvotes: 3

Related Questions