Select count satisfying 2 conditions?

Select count(*) from Merchant where Email in 
(Select SentEmail from MerchantInvitations where MerchantID = '16092') AND 
CreatedOn>  (Select TimeSent from MerchantInvitations where MerchantID = '开发者_如何学Go16092')

I want the count for Merchants satisfying whose email is in SentEmail column in MerchantInvitations and CreatedOn > Timesent in MerchantInvitations. But i get an error that "Subquery returned more than 1 value. This is permitted when the subquery follows =,!=,<,<=, etc or when the subquery is used as an expression" Help me out please!! Thanks in advance!

---

More than one entry in MerchantInvitations where MerchantID = '16092' would cause this problem. Maybe you want the most recent TimeSent, like this:

Select count(*) from Merchant where Email in 
(Select SentEmail from MerchantInvitations where MerchantID = '16092') AND 
CreatedOn>  (Select MAX(TimeSent) from MerchantInvitations where MerchantID = '16092')

---

If your second subquery returns more than one row, it will never work. You can use ANY or ALL keyword, though :

SELECT COUNT(*)
FROM Merchant
WHERE Email IN (SELECT SentEmail
               FROM MerchantInvitations
               WHERE MerchantID = '16092') -- Why not 16092? Is it really a string?
AND CreatedOn > ANY (SELECT TimeSent
                     FROM MerchantInvitations
                     WHERE MerchantID = '16092');

But it looks like you just want to do a simple join :

SELECT COUNT(*)
FROM Merchant M
INNER JOIN MerchantInvitations I
    ON M.MerchantID = I.MerchantID -- You may have to change this
    AND M.Email = I.SentEmail
    AND M.CreatedOn > I.TimeSent
WHERE M.MerchantID = 16092;

Which should definitely be faster than your subqueries.

---

Looks like you sub-query is returning more than one TimeSent which is compared with CreatedOn. To fix this add an ALL specifier as:

Select count(*) from Merchant where 
Email in (Select SentEmail from MerchantInvitations where MerchantID = '16092') AND 
CreatedOn > ALL (Select TimeSent from MerchantInvitations where MerchantID = '16092')

---

The problem is probably

 (Select TimeSent from MerchantInvitations where MerchantID = '16092')

If that sub-query returns more than one value, then how does the database engine know which to compare against CreatedOn?

You can fix the problem by using either MIN(TimeSent) or MAX(TimeSent) in your sub-query (in place of TimeSent), whichever is appropriate.

---

use where exists instead of in

Select count(*) from Merchant where exists
(Select SentEmail from MerchantInvitations where MerchantID = '16092' and MerchantInvitations.sentemail = merchant.email) AND 
CreatedOn>  (Select TimeSent from MerchantInvitations where MerchantID = '16092' and MerchantInvitations.sentemail = merchant.email)

---

Use a single EXISTS subquery:

Select count(*) 
from Merchant M
where exists
(select null
 from MerchantInvitations I
 where M.Email = I.SentEmail AND 
       M.CreatedOn > I.TimeSent AND
       I.MerchantID = '16092')