SQL subqueries 2 times.

Post Reply
mister_v
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

SQL subqueries 2 times.

Post by 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
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: SQL subqueries 2 times.

Post by 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
Post Reply