0

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

1

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.

0

Edit: didnt see that TypoCubeᵀᴹ already posted an answer before i did..

Test tables and data

CREATE TABLE A
    (`id` INT, `num` INT, `docnr` INT, `docy` VARCHAR(3))
;

INSERT INTO A
    (`id`, `num`, `docnr`, `docy`)
VALUES
    (1, 100, 563, 'abc'),
    (2, 50, 9978, 'abc'),
    (3, 150, 56, 'abc'),
    (4, 150, 679, 'abc'),
    (5, 150, 67, 'def'),
    (6, 150, 12, 'def')
;


CREATE TABLE B
    (`id` INT, `seq` INT, `toti` INT)
;

INSERT INTO B
    (`id`, `seq`, `toti`)
VALUES
    (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)
;

Two queries are possible here.

SELECT
   a.id 
 , a.docnr
 , a.docy
 # more columns 
FROM (

  SELECT 
     id
   , SUM(toti) AS sum_toti
  FROM 
   b
  GROUP BY
   id
  HAVING
    sum_toti > 585
  ORDER BY 
   id ASC 
) 
 AS b
INNER JOIN
 a
ON 
 b.id = a.id 

And

SELECT
   a.id 
 , a.docnr
 , a.docy
 # more columns 
FROM (

  SELECT 
     id
   , SUM(toti) AS sum_toti
  FROM 
   b
  GROUP BY
   id
  ORDER BY 
   id ASC 
) 
 AS b
INNER JOIN
 a
ON 
 b.id = a.id 
WHERE
  sum_toti > 585

Both queries need a multi column index to speed up the deliverd table execution speed

ALTER TABLE `b` ADD INDEX `id_toti` (`id`, `toti`); 

And ofcource indexes on the id columns on both tables.

  • 1
    The first query will be faster. The ORDER BY in the subquery will be ignored. – Rick James Dec 22 '16 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.