Combine subselect

I have to rewrite a query written for mysql5 to mysql4.0 which doesn't support sub select I have a query like this :

select a,
       c,
       (select count(*) from A) as e 
          from (select b, sum(e) as a 
                  from B 
                 where h = "foo" 
              group by b) TABLEB,
       (select sum(d),
               count(*) as c 
          from C 
         where d = "bar") TABLEC

I try to merge TABLEA and TABLE B in one request but sum() results are not correct ( sum(e) values become sum(e) multiplied by the number of rows of TABLEC)

All the grouped values become a multiple of the real values (depending on rows number).

Is is possible to transform this query into only one query for mysql 4.0 or will I have to split it into 3 query ?

Answers


Why in the world are you migrating to MySQL 4.0? It's ancient history, slow, buggy, and insecure. If you're using a hosting service that still runs MySQL 4.0, switch to a different hosting service.

But regardless of that, I see no reason why you combined these queries into one, even if you do use MySQL 5.0. The results from each table have no relationship to the others.

Just run three queries:

select b, sum(e) as esum from B where h = 'foo' group by b;

select sum(d) as dsum, count(*) as c from C where d = 'bar';

select count(*) as acount from A;

p.s.: Use single-quotes for string literals in SQL.


Need Your Help

Lambda capture parameter provoking ambiguity

c# lambda

I just bumped into a really strange C# behavior, and I’d be glad if someone could explain it to me.

Showing graphical interface from text editor when using vagrant ssh from cmd?

ubuntu ssh sublimetext2 vagrant

I am using vagrant ssh though cmd to a oracle virtual box running Ubuntu. I am trying to use sublime to edit my files through vagrant ssh but the application will not popup through cmd (running sub...