Webmaster2020.com

Posted: **Sat Feb 20, 2016 10:42 am**

Hello,

I have a query with twice the same sub-query.
Is there a way to do it only once?
The sub-query uses a value from the main-query.

Code: Select all

SELECT Bestel_id,klant.Klantnaam,
(SELECT count(*) FROM bestellijn WHERE bestellijn.Bestel_id=bestelling.Bestel_id) AS total_products
FROM bestelling,klant WHERE klant.Klant_id=bestelling.Klant_id AND (SELECT count(*) FROM bestellijn WHERE bestellijn.Bestel_id=bestelling.Bestel_id) >=3;

Posted: **Sat Feb 20, 2016 10:51 am**

Most RDBMS's should be able to optimize the query and will run the subquery only once.
So you don't need to worry about speed or anything like that.

You clould try assigning it to a variable

Code: Select all

WITH mySubQuery AS
(
  [the subquery code]
)

SELECT * FROM table WHERE  mySubQuery >= 3

But I don't think it will work in your example

Webmaster2020.com

SQL subqueries 2 times.

SQL subqueries 2 times.

Re: SQL subqueries 2 times.