INSERT .. SELECT multiple rows from multiple tables

Question

I need to record the number of items requested and issued against each item every day. The purchase_doc table is:

purchase_doc table

The requested_items table contains item requested as follows:

requested_items

The movement table contains item requested as follows:

movement

The need output (data to be inserted) is:

data_to_insert

One way of doing this is to fetch items issued and requested from the first 2 queries, and then build an array of items issued and requested against each item id, and then insert these values in the daily_movement table, like this:

SELECT n.item_id AS n__item_id, SUM(n.qty) AS qty
FROM requested_items n LEFT JOIN purchase_doc doc ON n.doc_id = doc.id 
WHERE (doc.type = 'Item Request' AND doc.created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)) 
GROUP BY n.item_id

SELECT n.item_id AS item_id, SUM(n.qty) AS qty
FROM movement n LEFT JOIN purchase_doc doc ON n.doc_id = doc.id 
WHERE (doc.type = 'Store Issue' AND doc.created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)) 
GROUP BY n.item_id

From these and other SELECTs, I need to insert a single row per item per day containing the qty of requests, issues, etc for this item in this fashion:

INSERT INTO daily_movement date, item_id, requested_qty, issued_qty VALUES ( NOW(), 23, 4, 5), ( NOW(), 25, 5, 5), ( NOW(), 113, 6, 8);

But there will be too many SELECTs (since I also need other activities performed per item), followed by an insert.

My question is: Is it possible to do this via a single SELECT ... INSERT statement. If not, can somebody suggest a more elegant way of doing this

Can you show how the fields you retrieve in the first two SELECTs relate to the fields in the INSERT? — DaveRandom
– DaveRandom, Commented Jul 25, 2012 at 14:49

DaveRandom · Accepted Answer · 2012-07-27 12:19:24Z

1

I'm thinking this, but it might be over-simplified:

INSERT INTO `daily_movement`
  (`date`, `item_id`, `requested_qty`, `issued_qty`)
SELECT NOW(), `r`.`item_id`, SUM(`r`.`qty`), SUM(`m`.`qty`)
  FROM `purchase_doc` `d`
  JOIN `requested_items` `r`
    ON `r`.`doc_id` = `d`.`id`
  LEFT JOIN `movement` `m`
    ON `m`.`doc_id` = `d`.`id`
WHERE
    (`d`.`type` = 'Item Request' OR `d`.`type` = 'Store Issue')
  AND
    `d`.`created_at` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY `r`.`item_id`

EDIT

This is my final answer, with a nasty UNION to get around MySQL's lack of FULL OUTER JOIN:

INSERT INTO `daily_movement`
  (`date`, `item_id`, `week_no`, `requested_qty`, `issued_qty`)
SELECT *
FROM (
  (
    SELECT COALESCE(`r`.`item_id`, `a`.`item_id`) AS `item_id`, CURDATE() AS `date`, NULL AS `week_no`, SUM(`r`.`qty`) AS `requests`, COALESCE(`a`.`issued`, 0) AS `issued`
    FROM `purchase_doc` `d`
    LEFT JOIN `requested_items` `r`
      ON `r`.`doc_id` = `d`.`id` 
    LEFT JOIN (
      SELECT `m`.`item_id`, SUM(`m`.`qty`) AS `issued`
      FROM `purchase_doc` `d`
      JOIN `movement` `m`
        ON `m`.`doc_id` = `d`.`id` 
      WHERE `d`.`type` = 'Store Issue'
        AND `d`.`created_at` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
      GROUP BY `m`.`item_id`
    ) `a`
      ON `a`.`item_id` = `r`.`item_id`
    WHERE `d`.`type` = 'Material Requisition'
      AND `d`.`created_at` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY `r`.`item_id`
  ) UNION DISTINCT (
    SELECT COALESCE(`m`.`item_id`, `a`.`item_id`) AS `item_id`, CURDATE() AS `date`, NULL AS `week_no`, COALESCE(`a`.`requests`, 0) AS `requests`, SUM(`m`.`qty`) AS `issued`
    FROM `purchase_doc` `d`
    LEFT JOIN `movement` `m`
      ON `m`.`doc_id` = `d`.`id` 
    LEFT JOIN (
      SELECT `r`.`item_id`, SUM(`r`.`qty`) AS `requests`
      FROM `purchase_doc` `d`
      JOIN `requested_items` `r`
        ON `r`.`doc_id` = `d`.`id` 
      WHERE `d`.`type` = 'Material Requisition'
        AND `d`.`created_at` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
      GROUP BY `r`.`item_id`
    ) `a`
      ON `a`.`item_id` = `m`.`item_id`
    WHERE `d`.`type` = 'Store Issue'
      AND `d`.`created_at` > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY `m`.`item_id`
  )
  ORDER BY `item_id`
) `u`

http://sqlfiddle.com/#!2/3923d/13

edited Jul 27, 2012 at 12:19

answered Jul 25, 2012 at 15:14

DaveRandom

88.8k11 gold badges159 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

qais Over a year ago

Doesn'k work. This way, I can only pick up values from either requested_items or movement tables. and the qty from the other table will get aggregated. The r.item_id in SELECT AND GROUP BY clause are creating the problem

qais Over a year ago

+1 as it's quite close to what I want. If requested_items or movement were a single table, it would have worked. Please see if you can correct this query to get the desired result

DaveRandom Over a year ago

@qais Sorry I don't quite follow what your problem is. Can you show some sample data from the three tables, the result this gives you at the moment and the result you want instead?

DaveRandom Over a year ago

@qais OK right, try this. It certainly produces the result set you want based on the data above, but I'm not sure whether it will work for every situation - to be honest I've been staring at it and fudging it about for so long I can't visualise it properly any more. I might pick it up and re-check it in the morning. Obviously that is a SELECT that demonstrates the result set that will be retrieved, you can easily add INSERT INTO above it for the actual query you execute.

DaveRandom Over a year ago

@qais OK I've woken up this morning and immediately realised what I don't like about this. What is really required here is a FULL OUTER JOIN between the outer query and the derived table of the inner query. As it is, any item_ids that produce a non-null result in the inner query but do not match that outer query (i.e. have a non-zero value for issued and a zero-value for requests) will not appear in the results. Unfortunately (and ridiculously) MySQL does not support full joins, you would have to union a left join with a right outer join, which makes this much less efficient.

|

Jonny · Accepted Answer · 2012-07-25 15:02:52Z

0

you can use the union operator here or here to get all your results in a single select

edited Jul 25, 2012 at 15:02

answered Jul 25, 2012 at 14:49

Jonny

2,93710 gold badges43 silver badges67 bronze badges

1 Comment

DaveRandom Over a year ago

UNION won't help with an INSERT ... SELECT. You need to JOIN the rows, not get more of them.

Devart · Accepted Answer · 2012-07-25 15:39:28Z

0

You can use a query like this -

edited:

INSERT INTO daily_movement(date, item_id, requested_qty, issued_qty)
SELECT i.item_id, SUM(ri.qty) requested_qty, SUM(m.qty) issued_qty FROM
  (SELECT item_id FROM requested_items UNION SELECT item_id FROM movement) i
  LEFT JOIN (
    SELECT n.item_id, n.qty
    FROM requested_items n LEFT JOIN purchase_doc doc ON n.doc_id = doc.id 
    WHERE doc.type = 'Item Request' AND doc.created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
  ) ri
  ON ri.item_id = i.item_id
  LEFT JOIN (
    SELECT n.item_id, n.qty
    FROM movement n LEFT JOIN purchase_doc doc ON n.doc_id = doc.id 
    WHERE doc.type = 'Store Issue' AND doc.created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
  ) m
  ON m.item_id = i.item_id
GROUP BY
  i.item_id;

edited Jul 25, 2012 at 15:39

answered Jul 25, 2012 at 14:58

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

DaveRandom Over a year ago

You didn't include the movement table anywhere

Devart Over a year ago

@DaveRandom Agree, I'll check it.

qais Over a year ago

Doesn'k work. Doesn't insert anything. The select statement alone (excluding the INSERT) gives incorrect output

Collectives™ on Stack Overflow

INSERT .. SELECT multiple rows from multiple tables

3 Answers 3

11 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related