MySQL – two tables with relation one to many – retrieve “n” fields from table1 WHERE SUM of a field on table2 is less/equal/greater than “value”

MySQLoptimizationperformancequery-performance

Tables structure:

CREATE TABLE IF NOT EXISTS `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(6) NOT NULL,
`docnr` int(6) NOT NULL,
`docy` varchar(3) NOT NULL,
`docd` date NOT NULL,
`ent` int(3) NOT NULL,
`paym` tinyint(1) NOT NULL,
`cli` int(6) NOT NULL,
`rags` varchar(40) NOT NULL,
`addr` varchar(30) NOT NULL,
`cap` varchar(5) NOT NULL,
`city` varchar(30) NOT NULL,
`pro` varchar(2) NOT NULL,
`naz` varchar(3) NOT NULL,
`telp` varchar(6) NOT NULL,
`teln` int(13) NOT NULL,
`rif` text NOT NULL,
`totd` decimal(12,2) NOT NULL DEFAULT '0.00',
`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `B` (
`id` int(11) NOT NULL,
`num` int(6) NOT NULL,
`seq` int(4) NOT NULL,
`typ` varchar(10) NOT NULL,
`art` int(6) NOT NULL,
`art_x` varchar(17) NOT NULL,
`cod` int(3) NOT NULL,
`descr` text NOT NULL,
`qty` decimal(11,4) NOT NULL,
`um` varchar(2) NOT NULL,
`val` decimal(12,2) NOT NULL,
`sc1` decimal(4,1) NOT NULL,
`sc2` decimal(4,1) NOT NULL,
`sc3` decimal(4,1) NOT NULL,
`iv` int(3) NOT NULL,
`tot` decimal(12,2) NOT NULL DEFAULT '0.00',
`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
KEY `idseq` (`id`,`seq`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have two tables, A and B;
the relation between them is one to many (Aone->Bmany) given by field "id";
I need to retrieve "n" fields from table A WHERE the SUM of the field B.toti on table B is less/equal/greater than an "x" given value;

Example:

table A
id | num | docnr | docy |
1  | 100 | 563   | abc  |
2  | 50  | 9978  | abc  |
3  | 150 | 56    | abc  |
4  | 150 | 679   | abc  |
5  | 150 | 67    | def  |
6  | 150 | 12    | def  |

table B
id | seq | toti
1  | 1   | 10
2  | 2   | 15
2  | 3   | 5
4  | 4   | 10
3  | 5   | 25
2  | 6   | 567
3  | 7   | 2354
4  | 8   | 768
1  | 88  | 456
1  | 33  | 30
1  | 35  | 89
4  | 31  | 4543
5  | 77  | 223
5  | 9   | 34
6  | 13  | 156
6  | 63  | 6758
5  | 93  | 6
6  | 53  | 22

So:

SUM of all B.toti with id=1 -> 585
SUM of all B.toti with id=2 -> 587
SUM of all B.toti with id=3 -> 2379
SUM of all B.toti with id=4 -> 5321
SUM of all B.toti with id=5 -> 263
etc..

I want to SELECT A.docnr, A.docy, A.paym, A.num WHERE the result of the sum above is less/equal/greater than "value" (passed to the query as STRING);

Using the sums above:
if the sum of all B.toti fields for that 'id' is greater than, let's say, '585' then retrieve 'n' fields on table A for the matching 'id', in this case retrieve the row on A for the 'id' 2,3,4;

How can I achieve this?
I already tried my own solution but it was a complete mess and soo slow!!
Consider that later I may need to retrieve some data from a couple of other tables (with a join), I prefer to specify this in the case it could be relevant for the solution;

Can't figure out a good or acceptable solution, please help me.
Cris

Best Answer

This is a rather simple GROUP BY / HAVING query in table B. You then only need to join table A to get the columns you want in the result:

SELECT a.*,                   -- pick the wanted columns from A
       bg.sum_toti             -- you can have this in the results, too,
                              -- if you want
FROM a
  JOIN
  ( SELECT id, SUM(toti) AS sum_toti
    FROM b
    GROYP BY id
    HAVING SUM(toti) >= ?              -- ? is the parameter, eg. 585
  ) AS bg
  ON bg.id = a.id ;

  • You already have an index on a (id). An index on b (id, toti) would help with efficiency.

  • I suggest you ditch MyISAM and use only InnoDB engine in your databases. It's the default engine in recent versions (5.6+) and gets far more development. MyISAM is pretty much a relic and offers no advantages (it used to have some, years ago), only disdvantages.

Related Question