SQL subqueries 2 times.

[mister_v]

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;

[chris]

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