CODEWITHSUNDEEP
mysqlsql
Sanju Menon
Sanju Menon

Reputation: 729

Can i use a select statement for a where condition

Is it possible to use a where condition with another select statement in a query. For example:

WHERE main_section ='Supplier' AND type_of_pay ='Advance' AND 
((select final_dateofpay FROM com_payments
WHERE main_section ='Supplier') IS NOT NULL)

Upvotes: 0

Views: 74

Answers (2)

geeksal
geeksal

Reputation: 5016

You cannot use a subquery like that in your where clause because your code

(select final_dateofpay FROM com_paymentsWHERE main_section ='Supplier') returns more than one row. So, it cannot be directly combined with IS NOT NULL . Remember where clause operates at row level.

However, I think that you want to check whether the subquery return results or not. In that case, you can use it like this

WHERE main_section ='Supplier' AND type_of_pay ='Advance' AND exists (select final_dateofpay FROM com_payments WHERE main_section ='Supplier')

Upvotes: 1

Matijs
Matijs

Reputation: 2553

The SQL standard allows a whole host of sub-query expressions with statements like IN (...), NOT IN (...) , EXISTS (...) and NOT EXISTS(...).

In this case the query would be:

WHERE main_section ='Supplier' AND type_of_pay ='Advance' AND 
  EXISTS (select final_dateofpay FROM com_payments
    WHERE main_section ='Supplier'))

The effect of this query would be that you either would get no rows because there doesn't exist any com_payments with main_section 'Supplier' or all original rows because one does exist.

The reason for this strange result is that you do not reference any fields from the containing select statement in your sub-query. When you add a filter in the sub-query where referencing the main query you will probably get the result you are aiming for.

Upvotes: 1

Related Questions