CODEWITHSUNDEEP
phpmysqlsql
qais
qais

Reputation: 1888

INSERT .. SELECT multiple rows from multiple tables

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

Upvotes: 0

Views: 2026

Answers (3)

DaveRandom
DaveRandom

Reputation: 88647

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

Upvotes: 1

Devart
Devart

Reputation: 121912

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;

Upvotes: 0

Jonny
Jonny

Reputation: 2917

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

Upvotes: 0

Related Questions